Excel VBA Find – How to find any value in a range of cells with VBA
Many times as a developer you might need to find a match to a particular value in a range or sheet, and this is often done using a loop. However, VBA provides a much more efficient way of accomplishing this using the Find method. In this article we’ll have a look at how to use the Range.Find Method in your VBA code. Here is the syntax of the method:
expression.Find (What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )
where:
What: The data to search for. It can be a string or any of the Microsoft Excel data types (the only required parameter, rest are all optional)
After: The cell after which you want the search to begin. It is a single cell which is excluded from search. Default value is the upper-left corner of the range specified.
LookIn: Look in formulas, values or notes using constants xlFormulas, xlValues, or xlNotes respectively.
LookAt: Look at a whole value of a cell or part of it (xlWhole or xlPart)
SearchOrder: Search can be by rows or columns (xlByRows or xlByColumns)
SearchDirection: Direction of search (xlNext, xlPrevious)
MatchCase: Case sensitive or the default insensitive (True or False)
MatchByte: Used only for double byte languages (True or False)
SearchFormat: Search by format (True or False)
All these parameters correspond to the find dialog box options in Excel.
Return Value: A Range object that represents the first cell where the data was found. (Nothing if match is not found).
Let us look at some examples on how to implement this. In all the examples below, we will be using the table below to perform the search on.
Example 1: A basic search
In this example, we will look for a value in the name column.
Dim foundRng As Range Set foundRng = Range("D3:D19").Find("Andrea") MsgBox foundRng.AddressThe rest of the parameters are optional. If you don’t use them then Find will use the existing settings. We’ll see more about this shortly.
The output of this code will be the first occurrence of the search string in the specified range.
If the search item is not found then Find returns an object set to Nothing. And an error will be thrown if you try to perform any operation on this (on foundRng in the above example)
So, it is always advisable to check whether the value is found before performing any further operations.
Dim foundRng As Range Set foundRng = Range("D3:D19").Find("Andrea") If foundRng Is Nothing Then MsgBox "Value not found" Else MsgBox foundRng.Address End IfLet us know have a look at the optional parameters. To keep it simple, we will exclude the above error handling in the subsequent examples.
Example 2: Using after
Set foundRng = Range("D3:D19").Find("Andrea", Range("D6"<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>)) MsgBox foundRng.AddressOR
Set foundRng = Range("D3:D19").Find("Andrea", After:=Range("D6")) MsgBox foundRng.AddressThe highlighted cell will be searched for
Example 3: Using LookIn
Before seeing an example, here are few things to note with LookIn
- Text is considered as a value as well as a formula
- xlNotes is same as xlComments
- Once you set the value of LookIn all subsequent searches will use this setting of LookIn (using VBA or Excel)
'Look in values Set foundRng = Range("A3:H19").Find("F4", , xlValues) MsgBox foundRng.Address 'Output --> $B$4 'Look in values and formula Set foundRng = Range("A3:H19").Find("F4", LookIn:=xlFormulas) MsgBox foundRng.Address 'Output --> $B$4 'Look in values and formula (as previous setting is preserved) Set foundRng = Range("H3:H19").Find("F4") MsgBox foundRng.Address 'Output --> $H$4 'Look in comments Set foundRng = Range("A3:H19").Find("F4", , xlNotes) MsgBox foundRng.Address 'Output --> $D$4Example 4: Using LookAt
'Match only a part of a cell Set foundRng = Range("A3:H19").Find("John", , xlValues, xlPart) MsgBox foundRng.Address 'Output --> $D$8 'Match entire cell contents Set foundRng = Range("A3:H19").Find("John", LookAt:=xlWhole) MsgBox foundRng.Address 'Output --> $D$9LookAt setting too is preserved for subsequent searches.
Example 5: Using SearchOrder
'Searches by rows Set foundRng = Range("A3:H19").Find("Sa", , , xlPart, xlRows) MsgBox foundRng.Address 'Output --> $G$4 'Searches by columns Set foundRng = Range("A3:H19").Find("Sa", SearchOrder:=xlColumns) MsgBox foundRng.Address 'Output --> $F$5SearchOrder setting is preserved for subsequent searches.
Example 6: Using SearchDirection
'Searches from the bottom Set foundRng = Range("A3:H19").Find("Alexander", , , , , xlPrevious) MsgBox foundRng.Address 'Output --> $D$18 'Searches from the top Set foundRng = Range("A3:H19").Find("Alexander", SearchDirection:=xlNext) MsgBox foundRng.Address 'Output --> $D$11Example 7: Using MatchCase
'Match Case Set foundRng = Range("A3:H19").Find("Sa", , , , xlRows, , True) MsgBox foundRng.Address 'Output --> $F$5 'Ignore case Set foundRng = Range("A3:H19").Find("Sa", MatchCase:=False) MsgBox foundRng.Address 'Output --> $G$4Example 8: Using SearchFormat
When you search for a format it is important to note that the format settings stick until you change them. So, before you use SearchFormat, it is a good practice to always clear any previous formats that have been set.
'Clear any previous format set Application.FindFormat.Clear 'Set the format for find operation Application.FindFormat.Interior.Color = 14348258 'Match format Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=True) MsgBox foundRng.Address 'Output --> $G$15 'Search without matching the format Set foundRng = Range("A3:H19").Find("SA", MatchCase:=True, SearchFormat:=False) MsgBox foundRng.Address 'Output --> $G$4 'Search only for cells of a particular format Set foundRng = Range("B3:H19").Find("*", MatchCase:=True, SearchFormat:=True) MsgBox foundRng.Address 'Output --> $B$3Example 9: FindNext and FindPrevious
In all the previous examples, we have been looking for just the first occurrence of the search criteria. If you want to find multiple occurrences, we use the FindNext and FindPrevious methods. Both of these need a reference to the last cell found, so that the search continues after that cell. If this argument is dropped, it will keep returning the first occurrence.
'First occurrence Set foundRng = Range("A3:H19").Find("Ms") MsgBox foundRng.Address 'Output --> $C$5 'Find the next occurrence Set foundRng = Range("A3:H19").FindNext(foundRng) MsgBox foundRng.Address 'Output --> $C$10 'Find the previous occurrence again Set foundRng = Range("A3:H19").FindPrevious(foundRng) MsgBox foundRng.Address 'Output --> $C$5If you are looking to find or list all files and directories in a folder / sub-folder, you can refer to the articles below:
Find and List all Files and Folders in a Directory
Excel VBA, Find and List All Files in a Directory and its Subdirectories
For using other methods in addition to Find, please see:
2 thoughts on “Excel VBA Find – How to find any value in a range of cells with VBA”