Excel makes same error every time.

[davidam]

Hello,
I wonder if anyone has seen this before, and if they know why it happens. I am using the Offset function to return values from a list. Something like this:

name = ActiveCell.Offset((Range("A1876").Value,0).Value
Set Loc = Range ("A61501:A65500").Find(What:=name, LookIn:=xlValues)
Range("A1").Value = Loc.Offset(0,1).Value

The list is something like this:

61500 Names Top Bottom
61501 ... X...... 1.... 2
61502 ... Y...... 3.....4

The list is quite long and every value that is returned is correct except for one: the first value. In this case it returns the a value which is one extra row down. If I change the look-up range to include the title row, ie "A61500:A65500" then it returns the correct value. Just wondering. Incidentally, I use the exact same code on three other lists and it only mistakes this mistake on one of them.
David

 

[Norie]

David

You aren't telling Find where to start looking.

So, and I'm not 100% sure about this, it's probably starting at the first cell after the first cell in the range it's looking in.

In this case that would be A61502.

Perhaps this will explain that better:

If you do no specify this argument, the search starts after the cell in the upper-left corner of the range.

 

[davidam]

AFTER the cell in the upper-left corner of the range...that would explain it. How do I specify the starting cell?

 

[Norie]

With the After argument, or you could just start the range a row up.

That shouldn't cause any problems because the search should start in the right place.

 

[davidam]

Right, makes sense.
Thanks!
David