using variables with range(cells) reference

[mhorstman]

Hi,

I'm trying to build a range based off of variables that are calculated by user input. I have been able to get everything to work except for the range itself. I keep getting the "method of range of object _worksheet failed" error. The input is on one worksheet and the range is on a separate worksheet. Any help would be greatly appreciated.

Thanks,

Matt

Dim Sh2 As Worksheet
Dim Measure As String
Dim List As Range
Set Sh2 = ThisWorkbook.Worksheets("Sheet2")
Measure = Cells(2, 9).Value
Dim listaddress As String
Set List = Sh2.Range("A1:Z500").Find(Measure, lookat:=xlPart)
listaddress = List.Address
Dim listc As Integer
listc = List.Column
MsgBox "List Address = " & listaddress
MsgBox "List Column = " & listc


Dim r As Range
Dim lastr As Integer
lastr = Sh2.Cells(Sh2.Rows.Count, listc).End(xlUp).Row
MsgBox "Lastr = " & lastr
Set r = Range(Sh2.Cells(2, listc), Sh2.Cells(lastr, listc))
MsgBox "r = " & r

 

[Norie]

Does this work?

With Sh2
    Set r = .Range(.Cells(2, listc), .Cells(lastr, listc))
End With