VBA - Lookup address

B

Brassman

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
 
T

Tom Ogilvy

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
 
Top