I have a VBA procedure at
http://www.cpearson.com/Excel/Findall.aspx
that will find all occurrences of a value in a specified range of
cells. As its result, it returns a Range object that references each
cell in which the value was found. Using that function (posted at the
end of this message), you can do something like:
Sub AAA()
Dim RangeToSearch As Range
Dim FindWhat As Variant
Dim FoundCells As Range
Dim R As Range
Set RangeToSearch = Worksheets("Sheet1").Range("A1:C20")
FindWhat = "abc"
Set FoundCells = FindAll(SearchRange:=RangeToSearch, _
FindWhat:=FindWhat, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCells Is Nothing Then
Debug.Print "not found"
Else
For Each R In FoundCells
Debug.Print FindWhat, "found in content '" & _
R.Text & "' in cell: ", R.Address
Next R
End If
End Sub
This code will use FindAll to set the variable FoundCells to all the
cells in which "abc" was found. With that variable, you can get both
the value of the cell and the address of the cell. This example just
lists the content and address of the found cells in the Immediate
Window, but you can do whatever you want with the Value and Address
properties of the cells in FoundCells.
You can download a bas module with the FindAll function at
http://www.cpearson.com/Excel/Findall.aspx or copy the code below. The
downloadable module has other related functions in addition to
FindAll, including an extension of FindAll to search multipe ranges
and multiple worksheets.
Function FindAll(SearchRange As Range, _
FindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlWhole, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional MatchCase As Boolean = False) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This searches the range specified by SearchRange and returns a Range
object
' that contains all the cells in which FindWhat was found. The search
parameters to
' this function have the same meaning and effect as they do with the
' Range.Find method. If the value was not found, the function return
Nothing.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
With SearchRange
Set LastCell = .Cells(.Cells.Count)
End With
'On Error Resume Next
On Error GoTo 0
Set FoundCell = SearchRange.Find(what:=FindWhat, _
after:=LastCell, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=SearchOrder, _
MatchCase:=MatchCase)
If Not FoundCell Is Nothing Then
Set FirstFound = FoundCell
Set ResultRange = FoundCell
Set FoundCell = SearchRange.FindNext(after:=FoundCell)
Do Until False ' Loop forever. We'll "Exit Do" when necessary.
If (FoundCell Is Nothing) Then
Exit Do
End If
If (FoundCell.Address = FirstFound.Address) Then
Exit Do
End If
Set ResultRange = Application.Union(ResultRange, FoundCell)
Set FoundCell = SearchRange.FindNext(after:=FoundCell)
Loop
End If
Set FindAll = ResultRange
End Function
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
