VBA - Lookup cell address
 

Is there a way to get vlookup to return the address
instead of the value of a cell. I can do this w/o VBA
using the function

=CELL("address",INDEX(C71:D74,MATCH("b",C71:C74),2 ))

but the CELL function is not in the WorksheetFunction
object. Thanks in advance for any help with this.

VBA - Lookup cell address
 

Is there any particular reason for not using

Range("A3").Address

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

skrev i en meddelelse
...
Is there a way to get vlookup to return the address
instead of the value of a cell. I can do this w/o VBA
using the function

=CELL("address",INDEX(C71:D74,MATCH("b",C71:C74),2 ))

but the CELL function is not in the WorksheetFunction
object. Thanks in advance for any help with this.

VBA - Lookup cell 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

wrote in message
...
Is there a way to get vlookup to return the address
instead of the value of a cell. I can do this w/o VBA
using the function

=CELL("address",INDEX(C71:D74,MATCH("b",C71:C74),2 ))

but the CELL function is not in the WorksheetFunction
object. Thanks in advance for any help with this.