M
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
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