method range failed

[davegb]

I'm getting a "method range of object worksheet failed" error at the
line marked:

Set rStart = Range("B4")
lRow = rStart.End(xlDown).Row - 1

Set rCty = Range(rStart, Cells(lRow, "B"))


Call ColUnHideAll

Set wkshtSource = ActiveSheet

Set wkshtNew = Workbooks.Add.Sheets("sheet1")

rCty.Copy Destination:= _
wkshtNew.Range("A2")

Set rTopCell = wkshtSource.Range("3:3").Find("top", LookIn:=xlValues,
lookat:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column
lStartRow = rTopCell.Row + 1
End If

Set rCtySts = wkshtSource.Range(Cells(lStartRow, lColTop), Cells(lRow,
lColTop + 1))<------ERROR

rCtySts.Copy Destination:=wkshtNew.Range("B2")

End Sub

A couple of lines above, method range of the same worksheet worked
fine. So what's wrong with having another range of the same worksheet?

 

[Bob Phillips]

It's hard to say without the data, but if the Find fails, then lColTop and
lStartRow don't get set, so could be zero, and I cannot see where lRow gets
set. Cells doesn't like a zero argument.

 

[davegb]

It's hard to say without the data, but if the Find fails, then lColTop and
lStartRow don't get set, so could be zero, and I cannot see where lRow gets
set. Cells doesn't like a zero argument.

I should have mentioned that the find works, and all variables are
correct. No zeros.

 

[George Nicholson]

Set rCtySts = wkshtSource.Range(Cells(lStartRow, lColTop), Cells(lRow,

lColTop + 1))<------ ERROR

where is Cells? what worksheet? If you provide that info the error will
probably will go away. As it stands Excel doesn't know what you want (which
is usually the case with that error message), so you need to give it more
info. In this case verbosity is your friend.

Something like:
wkshtSource.Range(wkshtSource.Cells(lStartRow, lColTop),
wkshtSource.Cells(lRow, lColTop + 1))

HTH,

 

[CBrine]

Try changing it to this.

Set rCtySts = wkshtSource.Range(wkshtsource.Cells(lStartRow, lColTop), wkshtSource.Cells(lRow,
lColTop + 1))

You don't have a fully qualified reference with without ID'ing the Worksheet.

HTH
Cal

 

[davegb]

where is Cells? what worksheet? If you provide that info the error will
probably will go away. As it stands Excel doesn't know what you want (which
is usually the case with that error message), so you need to give it more
info. In this case verbosity is your friend.

Something like:
wkshtSource.Range(wkshtSource.Cells(lStartRow, lColTop),
wkshtSource.Cells(lRow, lColTop + 1))

HTH,

Thanks, George! I thought that having wkshtSource.range told it where
the cells were.

 

[George Nicholson]

Glad to help.

I thought that having wkshtSource.range told it where
the cells were.

Whenever you get "Error 1004 'Method x of y failed'", double check your
assumptions. I find they are usually the culprit.

HTH,