ExcelBanter - Find / Search Function

At http://www.cpearson.com/Excel/RangeFind.htm, I have a VBA procedure
named "FindAll" that will find all occurrences of a value, either
within a specific range or on an entire worksheet. Using that code,
you can use the Worksheet_Change event procedure to detect when a cell
value has changed (in this example, A1) and automatically run FindAll
to return the cells in which the value within A1 was found. This
example code both Selects the found cells and displays the count of
found cells. The code below should be in the Sheet code module for
the appropriate worksheet. The FindAll function should be placed in a
regular code module (e.g., Module1) although it will work if placed in
the same Sheet code module.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''
Private Sub Worksheet_Change(ByVal Target As Range)

Dim R As Range
Dim FoundCells As Range

If Target.Cells.Count 1 Then
Exit Sub
End If
If StrComp("$A$1", Target.Address) < 0 Then
Exit Sub
End If
If Len(Target.Text) = 0 Then
Exit Sub
End If
' FindAll function at http://www.cpearson.com/Excel/RangeFind.htm
Set FoundCells = FindAll(SearchRange:=Me.Cells, _
FindWhat:=Target.Text, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCells Is Nothing Then
MsgBox "Value not found."
Exit Sub
End If

' only Found in Target. Get out.
If StrComp(FoundCells.Address, Target.Address, vbBinaryCompare) = 0
Then
Exit Sub
End If

' select FoundCells:
FoundCells.Select
' display count. Subtract 1 to remove Target from the count.
MsgBox "Text found in " & CStr(FoundCells.Count - 1) & " cells."
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

On Sun, 12 Oct 2008 11:54:05 -0700, Andrew
wrote:

I am trying to create a Search / Find funtion in Excel without using the Find
/ Replace function.

Would like to have cell where info (word or number) can be entered and then
search selected