VBA - Lookup address

[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

 

[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("D7174")(res).Address
else
msgbox "not found"
End Sub

 

[Brassman]

Thanks, works great