Find a value with a loop
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.
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
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
- 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
.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,
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.
The methods of
.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.