Probably that is the way to go. You will need to use an extra pair of
parentheses though like
LR = Range(Range("P20")).value & Rows.Count).End(xlUp).Row
You can sometimes get away with not qualifying range but normally I would
LR =
Worksheets("MyWorksheet").Range(Worksheets("MyWorksheet").Range("P20")).valu
e & Rows.Count).End(xlUp).Row
Or use a
With Worksheets("Myworksheet")
LR =.Range(.Range("P20")).value & .Rows.Count).End(xlUp).Row
End with
You could design a userform to collect the information or you could use an
input box which is easier to set up.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of Charlie
Sent: Tuesday, 26 July 2011 1:49 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] VBA 'Like' using Cell Value
Hi David
Now working excellent. Thanks.
To make the macro more flexible in terms of other worksheets etc. Is it
possible to have a userform or such, at the start of macro for user to :
Set the column letter for: LR = Range(" User can insert
column Letter for where the indicators are
Set the column letter for: IfLCase(Range("
Set the column letter for: WithRange(" User can insert the
column to be formatted
I am trying to do something similiar to the code that says
=(Range"p17").value. Where the macro refers to a cell in the sheet.
Say for first code:
LR = Range(Range"P20").value & Rows.Count).End(xlUp).Row
If user inserts into specific cell in sheet the column letter a userform
would not be necessary. Is this the way for me to approach it ?
Thanks
Charlie
Set the column number for
Set the colmn letter
From: David Grugeon
Sent: Monday, July 25, 2011 8:41 PM
To: ExcelVBA@yahoogroups.com
Subject: RE: [ExcelVBA] VBA 'Like' using Cell Value
Well if it is all in one line like that it will not like it.
You need
Sub size()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To LR If
LCase(Range("A" & i).Value) = (Range("p17").Value) Then With Range("b" & i)
'<Return> .Font.size = 20 '<Return> .Font.Italic = True '<Return> End With
End If Next i End Sub
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of Charlie
Sent: Monday, 25 July 2011 11:53 AM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] VBA 'Like' using Cell Value
Hi Dave and David
Have changed the code and I am not using wildcard in P17.
When F8 thru, it skips the With Range("b"........../etc and message is
Syntax error.
Any ideas ?
Thanks
Charlie
**************
Sub size()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To LR If
LCase(Range("A" & i).Value) = (Range("p17").Value) Then With Range("b" &
i).Font.size = 20.Font.Italic = True End With End If Next i End Sub
From: David Grugeon
Sent: Sunday, July 24, 2011 11:43 PM
To: ExcelVBA@yahoogroups.com
Subject: RE: [ExcelVBA] VBA 'Like' using Cell Value
Are you sure you want to use "Like" If you just want to look up whatever is
in cell P17, then use "=" instead of "Like". Like would only be relevant if
P17 contains wildcards as well as text.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of railroads@slingshot.co.nz
Sent: Sunday, 24 July 2011 4:52 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] VBA 'Like' using Cell Value
Hi
I want to replace this line of code that looks for "Chapter":
If LCase(Range("A" & i).Value) Like "chapter*" Then Range("b" & i).Font.size
= 20
This works well.
But I need the value in a cell to be looked for. Say cdell P17. Cell changes
value from time to time.
Have tried this code below but the code seems to miss the Like (Range("P17")
part and changes all rows to size = 20.
If LCase(Range("A" & i).Value) Like (Range("p17").Value) Then Range("b" &
i).Font.size = 20
Is there something not quite right?
Also I would like to add code re the font size to include Italics. Have
tried this below but the & seems to be the problem. How do I include extra
format codes.
.............Font.size = 20 & Font.Italic = True
Thanks
Charlie
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar