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.