retrieve row of vlookup result (vba)?

arongahagan

New Member
Joined
Dec 15, 2005
Messages
3
Hi;

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

for example, i have:

Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, arongahagan
Welcome to the Board !!!!!

take a look at FIND
Code:
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

New Member
Joined
Dec 15, 2005
Messages
3
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

New Member
Joined
Dec 15, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,110
Messages
5,570,250
Members
412,313
Latest member
pauloalex
Top