Useful code: find all the occurrences of a search string in a column

[Maury Markowitz]

I work with large sheets that are constructed in several steps using
VBA code. I have repeatedly faced the problem of quickly doing Find
operations within these sheets. One might imagine that Find is an
obvious solution, but in fact the behavior of Find is rather quirky
and often fails to find hits - when the row of column has zero height
for instance. After much experimentation, I've found a solution that
appears to work every time.

In the code below I first set the first and last row on the sheet - in
my case the first five rows have headers and totals, so I skip them. I
then use Match to find any hits in the "array". This may quick-exit if
there are no examples of the search term in the column. If a hit is
found, we then reset the "array" and try again. Note that is is very
important to reset the aHit to Nothing, otherwise it will simply
return the last one again. It is also important to remember that the
returned number is _within_ the array, meaning that the number it
returns has to be added to your starting point (note the obi-wan) to
find the actual row or column number. Finally, Match returns an error
if nothing is found (sigh), so the Resume Next is required.

Hope someone fines this useful!

searchCol = "A"
firstRow = 5
LastRow = sheet.Cells.Find(What:="*",
SearchDirection:=xlPrevious, searchorder:=xlByRows).row

On Error Resume Next
' prime the pump by doing the initial find
searchRange = sheet.Range(searchCol & firstRow & ":" &
acctCol & LastRow)
aHit = Application.WorksheetFunction.Match(LOOKINGFOR,
searchRange, 0)

' now loop over the hits until it fails to find any new
ones
While Not IsEmpty(aHit)
' do something here, the hit is in (firstRow + aHit -
1)

' now reset the search range to be one position beyond
the last hit
firstRow = aHit + firstRow
searchRange = sheet.Range(searchCol & firstRow & ":" &
searchCol & LastRow)

' clean out the last hit and try again
aHit = Nothing
aHit = Application.WorksheetFunction.Match(LOOKINGFOR,
searchRange, 0)
Wend
On Error GoTo 0

 

[meh2030]

I work with large sheets that are constructed in several steps using
VBA code. I have repeatedly faced the problem of quickly doing Find
operations within these sheets. One might imagine that Find is an
obvious solution, but in fact the behavior of Find is rather quirky
and often fails to find hits - when the row of column has zero height
for instance. After much experimentation, I've found a solution that
appears to work every time.

In the code below I first set the first and last row on the sheet - in
my case the first five rows have headers and totals, so I skip them. I
then use Match to find any hits in the "array". This may quick-exit if
there are no examples of the search term in the column. If a hit is
found, we then reset the "array" and try again. Note that is is very
important to reset the aHit to Nothing, otherwise it will simply
return the last one again. It is also important to remember that the
returned number is _within_ the array, meaning that the number it
returns has to be added to your starting point (note the obi-wan) to
find the actual row or column number. Finally, Match returns an error
if nothing is found (sigh), so the Resume Next is required.

Hope someone fines this useful!

            searchCol = "A"
            firstRow = 5
            LastRow = sheet.Cells.Find(What:="*",
SearchDirection:=xlPrevious, searchorder:=xlByRows).row

On Error Resume Next
            ' prime the pump by doing the initial find
            searchRange = sheet.Range(searchCol & firstRow & ":" &
acctCol & LastRow)
            aHit = Application.WorksheetFunction.Match(LOOKINGFOR,
searchRange, 0)

            ' now loop over the hits until it fails to find any new
ones
            While Not IsEmpty(aHit)
                ' do something here, the hit is in (firstRow + aHit -
1)

                ' now reset the search range to be one position beyond
the last hit
                firstRow = aHit + firstRow
                searchRange = sheet.Range(searchCol & firstRow & ":" &
searchCol & LastRow)

                ' clean out the last hit and try again
                aHit = Nothing
                aHit = Application.WorksheetFunction.Match(LOOKINGFOR,
searchRange, 0)
            Wend
On Error GoTo 0

Maury,

I know how frustrating this was when I first encountered it; I'm glad
that you were able to come up with a solution. I've added another
snapshot of what I have come up with regarding this issue of finding
an item within a certain range. The Find and FindNext methods will
also accommodate what you are doing with your procedure. I adapted my
code slightly to align with your Column A criteria, and I simply
created a MsgBox output for the results.

Also rather than using Resume Next for your On Error statement (which
results from the MATCH not being able to match), you could use an If
Then to test the error, e.g. If IsError(aHit) Then, or If Not IsError
(aHit) Then.

Best,

Matt Herbert


Sub CustomFindTwo()

Dim rngSearch As Range
Dim rngUsed As Range
Dim rngFirstCell As Range
Dim rngLastCell As Range
Dim rngFound As Range
Dim rngFirstFound As Range
Dim rngListFound As Range
Dim lngFirstRow As Long
Dim lngLastRow As Long
Dim wksSearch As Worksheet
Dim strSearchCol As String
Dim varFindWhat As Variant

'set the worksheet to search in as an object
Set wksSearch = ActiveSheet

'set the column to look in
strSearchCol = "A"

'set the first row to look in
lngFirstRow = 5

'get the last row on the worksheet from the UsedRange
Set rngUsed = wksSearch.UsedRange
With rngUsed
lngLastRow = .Cells(.Cells.Count).Row
End With

'set the first cell for rngSearch
Set rngFirstCell = Range(strSearchCol & lngFirstRow)

'set the last cell for rngSearch
Set rngLastCell = Range(strSearchCol & lngLastRow)

'set rngSearch, i.e. where to look at for the "What"
' argument in the Find method
Set rngSearch = Range(rngFirstCell, rngLastCell)

'define the "What" for the Find method
varFindWhat = "Maury"

'see "Remarks" in Find Method help documentation for choosen
' arguments in the Find Method
Set rngFound = rngSearch.Find(What:=varFindWhat, _
After:=rngLastCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows)

'test if varFindWhat is found, if it is found then continue
If Not rngFound Is Nothing Then

'rngFirstFound acts as a "marker" to identify when
' you have looped through all possible finds, i.e.
' you are back at the beginning again
Set rngFirstFound = rngFound

'if there is only one item that is found then the one
' item is the result of the find
Set rngListFound = rngFound

'get the next find; this may or may not exist;
' see FindNext help documentation (FindNext preserves
' the Find arguments so they don't have to be reset)
Set rngFound = rngSearch.FindNext(After:=rngFound)

'loop for all possible finds
Do
'catch if there is one item found as well as
' determine if you are at the beginning "marker"
' of rngSearch
If rngFound.Address = rngFirstFound.Address Then
Exit Do
End If

'add the multiple found ranges into the rngListFound;
' Union appends the new found item range to the
' existing found item range
Set rngListFound = Application.Union(rngListFound, rngFound)

'since you are in a loop, we need to set the rngFound to the
' "next Find"; this will either be the next Find or it will
' be the range for the rngFirstFound
Set rngFound = rngSearch.FindNext(After:=rngFound)

Loop
End If

'do something with the result
If rngFound Is Nothing Then
MsgBox varFindWhat & " was not found."
Else
MsgBox "Found the following:" & vbLf & vbLf & rngListFound.Address
'rngListFound.Interior.ColorIndex = 36
End If

End Sub