Find fails with variable in range

[davidam]

Hello All,
Can anyone tell me why the following fails? I am trying to improve efficiency by replacing part of the 'Find' range with the variable lRow, but it fails unless I use a specified range.

Dim name As String
Dim Loc As Range
Dim x As Integer
Dim lRow As Integer
On Error Resume Next
x = 0
lRow = 61501
Do Until x = 251
name = ActiveCell.Offset((addRows + 1) * -1, 0).Value 
Set Loc = Range("A" & lRow - 5  & ":A65500").Find(What:=name, LookIn:=xlValues, Lookat:=xlWhole)
ActiveCell.Value = Loc.Offset(0, cCol).Value
x = x + 1
lRow = Loc.Row
ActiveCell.Offset(0, 1).Select

If I change the range back to "A61500:A65500" it works just fine.
Thanks,
David

 

[cstimart]

Hello All,
Can anyone tell me why the following fails? I am trying to improve efficiency by replacing part of the 'Find' range with the variable lRow, but it fails unless I use a specified range.

Dim name As String
Dim Loc As Range
Dim x As Integer
Dim lRow As Integer
On Error Resume Next
x = 0
lRow = 61501
Do Until x = 251
name = ActiveCell.Offset((addRows + 1) * -1, 0).Value 
Set Loc = Range("A" & lRow - 5  & ":A65500").Find(What:=name, LookIn:=xlValues, Lookat:=xlWhole)
ActiveCell.Value = Loc.Offset(0, cCol).Value
x = x + 1
lRow = Loc.Row
ActiveCell.Offset(0, 1).Select

If I change the range back to "A61500:A65500" it works just fine.
Thanks,
David

...but lRow - 5 = 61496

 

[davidam]

Yes, the top five will be blank...I do not think that is the problem as it fails if I use "A" &lRow & A65500" as well.

 

[cstimart]

I thru that code in a sub and it ran (except for the missing 'Loop'). Granted, I have nothing on my worksheet.

 

[davidam]

Thanks...I tested all the parts of it and they were fine too. It should work but it does not. Oh well...I will just run it as is...it works but a bit slow.

 

[davidam]

Found the problem: lRow has a value of about 61500...therefore it must be Dim as Long