Set Range for VLOOKUP

[music_al]

Hi

Im trying to set a range to be used in a VLOOKUP function. I cant figure out why this isn't working...
The RowCount variable is holding an Integer.

Dim MyRange as Range
Set MyRange = Worksheets("Anysheet").Range(Cells(2, 2), Cells(RowCount,5))

Thank you in advance.

 

[Fluff]

You need to qualify the cells like

Set MyRange = Worksheets("Anysheet").Range(Worksheets("Anysheet").Cells(2, 2), Worksheets("Anysheet").Cells(RowCount, 5))

or slightly simpler

Dim MyRange As Range
With Worksheets("Anysheet")
   Set MyRange = .Range(.Cells(2, 2), .Cells(RowCount, 5))
End With

 

[JackDanIce]

When the code runs, is "Anysheet" the activesheet? I think the issue is being caused by Cells(2,2) and Cells(RowCount, 5) not knowing what sheet they belong to.

Below works for me, regardless of which sheet is active, check difference in syntax to yours:

Sub test()
Dim RowCount As Integer: RowCount = 20
Dim MyRange As Range


Set MyRange = Range(Worksheets("MonkeyTennis").Cells(2, 2), Worksheets("MonkeyTennis").Cells(RowCount, 5))
MsgBox MyRange.Parent.Name & " " & MyRange.Address 'MsgBox displays "MonkeyTennis $B$2:$E$20"

Set MyRange = Nothing
End Sub

PS Integer data types in VBA are virtually redundant, you're better off declaring them as longs
and PPS beaten2it