Returning a Cell Address
 

Just want to know how to search a worksheet for a name (for instance) and
return the address of the cell.

Returning a Cell Address
 

If the value to be found occurs only once (or you are interested in
only the first occurrence), use


Sub AAA()
Dim FoundCell As Range
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
' search entire sheet for "abc"
Set FoundCell = WS.UsedRange.Find(what:="abc", LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
If FoundCell Is Nothing Then
Debug.Print "not found"
Else
Debug.Print "Found in cell: " & FoundCell.Address
End If
End Sub


If you want to find all the occurrences, you can use the FindAll
procedure at http://www.cpearson.com/Excel/findall.aspx , which
returns a Range object containing all of the cells in which the value
was found. You can then loop through the results of FindAll to get
all the occurrences:

Dim FoundCells As Range
Dim R As Range
Set FoundCells = FindAll(...)
If Not FoundCells Is Nothing Then
For Each R In FoundCells
Debug.Print FoundCell.Address,FoundCell.Value
Next R
End If

I also have a graphical user interface for FindAll implemented as an
xla add-in at http://www.cpearson.com/Excel/findallxla.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 27 Jan 2009 13:33:02 -0800, wlmjayhd
wrote:

Just want to know how to search a worksheet for a name (for instance) and
return the address of the cell.