VBA – Search using a loop or .Find

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 –

1. If A2 = 988000 then Debug.Print = $A$3
2. If A3 = 988000 then Debug.Print = $A$3
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.