range variable in excel

[michoohol]

I need to select dynamic range for formatting. How can I use a variable in VBA for this purpose?
refRow = findLastRow
targetRange = "G3:H" & Format(refRow)
ActiveSheet.Range(targetRange).Select
This code does not work.
Could you help me this?

 

[bryanbayfield]

What is findlastrow meant to be? Is it a variable of Range type? If so, you don't use an assignment statement, you use a set statement:

set object = otherobject

targetrange I'm guessing is meant to be a string. You're passing a range object to the format function (which takes two arguments, firstly a string, or something that can be converted to a string, secondly, a specific format).

If you actually got a valid range address into targetrange then teh third line would work. It's in the two previous lines it's not working. What are those two lines actually meant to od?

 

[michoohol]

findLastRow is a function as follows
Function findLastRow() As Long
Dim lastRow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox lastRow
End If
End Function
With this, I would like to set a range.
Is it enough to get a help from you?

 

[bryanbayfield]

Your function isn't returning anything. As part of your if block you should put findlastrow = lastrow. The search is also, I suspect wrong - you're starting in a2 and counting upwards when you want to start at the bottom row presumably and look up. Then remove format and the brackets from the second row and you should be there.

 

[bryanbayfield]

You may like to have a look at the FAQ, which offers solutions to the problem you're trying to solve with your code:

http://www.tek-tips.com/faqs.cfm?spid=707&sfid=2115