Find a value with a loop
Grab a copy of this example workbook
Say you have a list of organizations and their member counts –
For this example we will work with a list of 30, but the same concepts apply for a list of 30,000,000
If you want to find the organization with 988,000 members, you might default back to using a loop –
Sub FindLoop()
Dim i As Long
For i = 2 To 31
If Sheet1.Cells(i, 1) = 988000 Then Debug.Print Sheet1.Cells(i, 2)
Next
End Sub
This is pretty straight-forward and doesn’t take much time, even with a much larger population. However, this will Debug.Print
all Organizations with 988,000 members. If you want just the first one, you would need to alter your loop –
For i = 2 To 31
If Sheet1.Cells(i, 1) = 988000 Then
Debug.Print Sheet1.Cells(i, 2)
Exit For
End If
Next
But, if you imagine a much more complex function than Debug.Print
, your code starts to get wonky when you add conditions. So, if you’re wondering how you could make this simple loop more efficient, continue reading.
Range.Find ↗
As you may have noticed, .Find
returns a Range Object rather than a Value. The arguments for the method are –
*expression*.Find(What, After, [LookIn], [LookAt], [SearchOrder], [SearchDirection], [MatchCase], [MatchByte], [SearchFormat])
- What: The only required argument. The data to search for
- After: The single cell after which to begin the search. It is not included, Note: if your expression is a specific range, After must be within that range
- LookIn: What to look in – formulas (xlFormulas), values (xlValues) or comments (xlNotes or xlComments)
- LookAt: Match the search value to any part of the elements of the range (xlPart), or only to elements of the range that are whole (xlWhole)
- SearchOrder: Search each row in order (xlByRows), or search each columns in order (xlByColumns)
- SearchDirection: Search left to right / top to bottom (xlNext), or right to left / bottom to top (xlPrevious)
- MatchCase: Boolean. Case sensitive or not.
- MatchByte: Boolean. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.
- SearchFormat: If the format of the cell is important, specify the format
Now if we want to look for 988000, we use the .Find
method –
Sub FindExpression()
Dim searchRange As Range
Set searchRange = Sheet1.Range("A2:A31")
Dim foundCell As Range
Set foundCell = searchRange.Find(988000, After:=Sheet1.Range("A2"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, _
SearchFormat:=False)
Debug.Print foundCell.Address
End Sub
Once foundCell
is set, the .Find
operation stops. Consider these scenarios –
- If A2 = 988000 then Debug.Print = $A$3
- If A3 = 988000 then Debug.Print = $A$3
- If A3 <> 988000 and A31 = 988000 then Debug.Print = $A$18
Range.FindNext ↗
expression.FindNext(After)
- After is optional and has the same meaning as it does in .Find
The first thing you may notice is that there is no argument for What to find. .FindNext
should be used in conjunction with .Find
. This is similar to the first example, finding all Organizations with 988,000 members –
Sub FindNextExpression()
Dim searchRange As Range
Set searchRange = Sheet1.Range("A2:A31")
Dim foundCell As Range
Dim firstFound As Range
Set foundCell = searchRange.Find(988000, After:=Sheet1.Range("A2"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, _
SearchFormat:=False)
If Not foundCell Is Nothing Then
Debug.Print foundCell.Address
Set firstFound = foundCell
Do
Set foundCell = searchRange.FindNext(foundCell)
If Not foundCell Is Nothing And Not foundCell.Address = firstFound.Address Then
Debug.Print foundCell.Address
Else: Exit Do
End If
Loop
End If
End Sub
Range.FindPrevious ↗
The .FindPrevious
method is one strange bird. In some cases it will act exactly as .FindNext
; for example, in the example above you can change SearchDirection
and use .FindPrevious
to obtain the same results. Change one or both and watch what happens.
According to the msdn, .FindPrevious
–
Continues a search that was begun with the Find method. Finds the previous cell that matches those same conditions and returns a Range object that represents that cell.
Conclusion
The methods of .Find
and .FindNext
can be used instead of For
loops in many cases. It should be more efficient than the For
loop and can have significant performance impact on huge data sets.