VBA - Lookup address
 

I need to be able to look up a cell address.

I can lookup the value using Application.worksheetfunction.vlookup bu
I need the address. If this data

A 3
B 4
C 5

were in the range A1:B3, and the lookup value was in column A, I nee
to know the address of the cell in the same row in column B.

I figured out a way to do it in Excel, but it doesn't work in VB
because it uses the CELL function which isn't in the WorksheetFunctio
object. Any help is appreciated. Thanks

--
Message posted from http://www.ExcelForum.com

VBA - Lookup address
 

Dim rng as Range, res as variant
rng = Range("C71:C74")
res = application.Match("b",rng,0)
if not iserror(res) then
msgbox Range("D71:D74")(res).Address
else
msgbox "not found"
End Sub

--
Regards,
Tom Ogilvy

"Brassman " wrote in message
...
I need to be able to look up a cell address.

I can lookup the value using Application.worksheetfunction.vlookup but
I need the address. If this data

A 3
B 4
C 5

were in the range A1:B3, and the lookup value was in column A, I need
to know the address of the cell in the same row in column B.

I figured out a way to do it in Excel, but it doesn't work in VBA
because it uses the CELL function which isn't in the WorksheetFunction
object. Any help is appreciated. Thanks.


---
Message posted from http://www.ExcelForum.com/

VBA - Lookup address
 

Thanks, works great

--
Message posted from http://www.ExcelForum.com