"Method 'Range' of object '_Worksheet' failed" Error !!!!!!

Question

What am I doing wrong  here. The highlighted code line below is giving me a "Method 'Range' of object '_Worksheet' failed" error.

Dim UTws As Worksheet

Dim UTlRow As LongDim  countRange As String

Set UTws = Worksheets("Upload Tracker")

UTlCol = UTws.Cells(1, Columns.Count).End(xlToLeft).Column

countRange = .Range(Cells(10, 9), Cells(10, UTlCol)).Address

Answer 1

···

countRange = .Range(Cells(10, 9), Cells(10, UTlCol)).Address···

You might be suffering from Copy&Paste-itis. The .Range( property starts with a period (i.e. full stop). This is meant to be used inside a a With ... End With wrapper that tells it where its parent is. However, if you used one you would also have to insert a period before both of the Cells(.. properties in order that they know where their parents are.

As it sits, it should work if you change .Range(... to Range(... (without the period) as long as Worksheets("Upload Tracker") is the ActiveSheet. If you need the wrapper then try something like,

Dim UTws As Worksheet

Dim UTlRow As LongDim  countRange As String

Set UTws = Worksheets("Upload Tracker")

With UTws

    UTlCol = .Cells(1, Columns.Count).End(xlToLeft).Column

    countRange = .Range(.Cells(10, 9), .Cells(10, UTlCol)).Address

End With

Answer 2

Thanks Jeeped, that seem to do the trick. Now I need to figure out how to get my range:

 countRange = .Range(.Cells(10, 9), .Cells(10, UTlCol)).Address

in the form

$I10:$P10

instead of

$I$10:$P$10.

UPdate:

found the answer.

countRange = .Range(.Cells(10, 9), .Cells(10, UTlCol)).Address(RowAbsolute:=False)

Thanks.