To find all the cells in a particular range that contain certain text, you can loop through all the cells in the range and test each cell’s Value property. But if the range is large, a loop like this can be slow. One alternative is the Find method. It is the VBA implementation of Edit > Find that we’ve all used in Excel’s user interface.
The Find method can be considerably quicker than looping, but there are a couple of things that you should know about Find. First, it will find the first cell in the range that meets your criteria. The FindNext method can then be used to find the next occurance. However, after FindNext has found the last occurence, executing it again will return to the start and find the first occurence all over again. This means that if you intend to find all ocurrences, you need to include a way for the cycle to stop.
The second thing you should know is how Excel treats Find’s arguments. Most methods have default argument values, so that when you omit them, you still know what they will be. Find is different in that it’s default arguments change every time it’s used. One of the arguments is LookAt and it’s used to identify whether you’re looking at whole cells or just part of the cells. If you execute Find and specify a LookAt of xlPart, then xlPart becomes the default for that argument. If you execute Find again and omit the LookAt argument, it will default to xlPart, which may be what you want. But maybe not – it pays to know.
I generally specify any arguments that are important to me and don’t rely on the default. LookAt is an example of an argument that I always specify. SearchOrder is an example of one that I rarely specify because, in most cases, I don’t care if it looks by rows or by columns. Be sure to check help to see all the arguments and what they do.
You should also note that the “shifting default” discussed above is not changed just by using Find in VBA. It is also changed when Edit > Find is used in the UI. That makes it particularly dangerous to rely on the defaults.
How about an example? Assume we have worksheet with a regional sales table on it. In column A is the region name and we need to highlight all the rows for the North region. Here’s one way you might do it.
Dim rFound As Range
Dim rSearch As Range
Dim sFirstAddress As String
'Set search range to column A
Set rSearch = Intersect(wksSales.UsedRange, wksSales.Columns(1))
'Find the first occurance of North
Set rFound = rSearch.Find(what:="North", _
'If North was found
If Not rFound Is Nothing Then
'Store the address in a variable
sFirstAddress = rFound.Address
'Start a loop
'Color the row of the found cell
rFound.EntireRow.Interior.ColorIndex = 6
'Find the next cell with North
Set rFound = rSearch.FindNext(rFound)
'Stop when Find loops back to the first cell found
Loop Until rFound.Address = sFirstAddress