method range failed

  • Thread starter Thread starter davegb
  • Start date Start date
D

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?
 
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.
 
Bob said:
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.
Click to expand...

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