retrieve row of vlookup result (vba)?

[arongahagan]

Hi;

In VBA, is there any way to access the row (or the cell address) of the vlookup result?

for example, i have:

dim v as double
v = application.vlookup(ein_index,table_array,column_index,false)

...to get the value of the cell. i already know the column_index, but what i need is the row (or address) of that cell...is this possible? any creative geniuses out there?

I'm running Excel 2003 on XP pro.

TIA,
AG

 

[erik.van.geit]

Hello, arongahagan
Welcome to the Board !!!!!

take a look at FIND

Sub test()
Dim c As Range
Dim R As Long

Set c = YourRange.Find(YourValue) 'add other arguments see helpfiles for FIND

If Not c Is Nothing Then R = c.Row
End Sub

kind regards,
Erik

 

[arongahagan]

Yes, that would work nicely--except I need to be certain I'm getting the exact same cell that this vlookup formula is returning, in order to change its value if necessary...

thanks for the reply - any more ideas, i'm all ears!

AG

 

[erik.van.geit]

I need to be certain I'm getting the exact same cell that this vlookup formula is returning

are there more instances of the value to find ?
if only one there wouldn't be a problem

so time to provide a little example ...

best regards,
Erik

 

[arongahagan]

unfortunately, yes, there are multiple values (data scrubbing isn't fun at all!) Then again, I have validation earlier in the code to ensure i'm not looking at a duplicate value...so the FIND function will work, after all!

Thanks, Erik, and Merry Christmas!
AG