VBA Cell Address

S

scottfoxall

I run a macro to find a value/text in a spread sheet. Instead of getting the
value from that cell, I want the address. Is there code that can do this?
 
D

Dave

This code looks for the string "asdf" in the current worksheet and
displays a message box with the address when it is found. If the
string is not found it advises the user, as well. You can substitute
your search string as a variable or as a hardcoded entry.

Sub Find()
On Error GoTo NotFound:
Cells.Find(What:="asdf", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

MsgBox ActiveCell.Address
End

NotFound:
MsgBox ("Search target not found.")

End Sub
 
Top