You can use the ‘Range.Find’ method in Excel VBA to find a value in a given cell range. Using the ‘Range.Find’ method in Excel VBA is, in effect, the same as using Excel’s ‘Find and Replace’ feature.
In this tutorial, I will show you some examples of using VBA to find a value in a range.
The ‘Range.Find’ Method
The ‘Range.Find’ method locates specific information within a given cell range.
Below is the syntax of the Range.Find method in VBA:
expression.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Note: All the nine method parameters are of the Variant data type and are optional except the ‘What’ parameter, which is required.
The table below summarizes the method’s arguments:
Argument | Description |
---|---|
What | The data to search for. |
After | The cell after which you want the search to start. The After argument must be a single cell in the range. The search starts after this cell and is only searched once the code returns to this cell.If you omit this argument, the search begins after the cell in the upper-left corner of the range. |
LookIn | It can be one of the following constants: xlValues, xlFormulas, xlComments, or xlCommentsThreaded. |
LookAt | It can be one of the following constants: xlPart or xlWhole. |
SearchOrder | It can be one of the following constants: xlByColumns or xlByRows. |
SearchDirection | It can be one of the following constants: xlPrevious or xlNext. |
MatchCase | The default value is ‘False’ to make the search case-insensitive. Use ‘True’ to make the search case-sensitive. |
MatchByte | It is only applicable if you have selected or installed double-byte language support. Set ‘False’ to have double-byte characters match their single-byte equivalents. Use ‘True’ to have double-byte characters match only double-byte characters. |
SearchFormat | The format of the range, e.g., bold text. |
Important facts to consider when using the method:
- The method does not affect the active cell or the selection and returns ‘Nothing’ if no match is found.
- The settings for ‘LookIn,’ ‘LookAt,’ ‘SearchOrder,’ and ‘MatchByte’ are saved each time you use the ‘Range.Find’ method. If you don’t provide new values for these arguments, the next time you call the method, the saved values are used. For instance, if you set the ‘LookIn’ parameter to ‘xlCommentsThreaded,’ the method will only search for a value in comments. The next time you call the method, the existing setting for the ‘LookIn’ parameter will be comments. Therefore, it’s best to set these arguments explicitly every time you use the ‘Range.Find’ method to prevent any issues.
Also read: VBA to Find Last Row in Excel
VBA to Find a Single Value in a Range
Let’s now look at some examples where we will use VBA to find a single cell based on criteria.
Example #1: VBA to Find the First Occurrence of a Value in a Range
Suppose you have the following dataset on the active sheet.
You can use the VBA code below to search for ‘Switzerland’ in the dataset:
Sub FindValueFirstOccurence()
Dim rgFound As Range
Set rgFound = Range("A2:A9").Find("Switzerland")
Debug.Print "Value found in:" & rgFound.Address
End Sub
When you run the code, it prints the address of the cell containing ‘Switzerland’ to the Immediate Window:
Example #2: VBA to Find the Second Occurrence of a Value in a Range
Assume you have the dataset below on the active sheet:
Notice that the value ‘Switzerland’ appears twice in the dataset.
You can use the following subroutine to find the second instance of ‘Switzerland’ in the dataset:
Sub FindValueSecondOccurence()
Dim cellFound As Range
Set cellFound = Range("A2:A10").Find("Switzerland", After:=Range("A6"))
Debug.Print "Value found in:" & cellFound.Address
End Sub
When you execute the code, it prints the address of the cell containing the second occurrence of ‘Switzerland’ to the Immediate Window:
I set the ‘After’ parameter to ‘Range(“6”) to ensure the code begins the search after the first occurrence of ‘Switzerland.’
Note: If the code does not find the second occurrence of the value, it will wrap around or go back to the start of the range and continue the search. In the dataset below, ‘Switzerland’ does not appear after cell A6. Therefore, the code will return to the start of the cell range A2:A10 and return the address of cell A6, which contains ‘Switzerland.’
Example #3: VBA to Find a Value Based on a Formula in a Range
Sometimes, the values in a range are based on formulas.
For example, in the dataset below on Sheet3 of the current workbook, the value ‘Orange’ in cell A1 is static, but the value ‘Orange’ in cell A2 is based on a formula.
You can use the VBA code below to find the ‘Orange’ based on a formula in the dataset:
Sub FindFormulaBasedValue()
Dim rngFound As Range
Set rngFound = Worksheets("Sheet3").Range("A1:A2").Find("Orange", LookIn:=xlFormulas)
Debug.Print "Found 'Orange' as formula in: " & rngFound.Address
End Sub
When you execute the subroutine, it prints the address of the cell containing ‘Orange’ based on a formula to the Immediate Window:
Example #4: VBA to Find a Threaded Comment in a Range
You can use comments in Excel to discuss with others you are collaborating with about the data you are working on. In Excel 365, you can use comments for discussions with others and notes for annotations.
Suppose on Sheet4, you have the dataset below with threaded comments in cells B2 and B4 and a note in cell B3:
You can use the VBA code below to find the comment containing ‘Peter’ in the cell range B2:B4:
Sub FindThreadedComment()
Dim rgFound As Range
Set rgFound = Worksheets("Sheet4").Range("B2:B4").Find("Peter", LookIn:=xlCommentsThreaded, LookAt:=xlPart)
Debug.Print "Found 'Peter' in threaded comment in: " & rgFound.Address
End Sub
When you run the code, it prints the address of the cell with the comment containing ‘Peter’ to the Immediate Window:
Notice we set the ‘LookAt’ argument to ‘xlPart’ because the search value must only match part of the comment.
Note: If you want to find a note in the range, set the ‘LookIn’ argument to ‘xlComments.’
Example #5: VBA to Find a Value in a Range Using Wildcard Characters
The asterisk (*) wildcard character represents any number of characters, and the question mark (?) represents any one character. In this example, I will use the asterisk character.
Assume you have the following dataset on Sheet4:
You can find the state on the list that begins with the letter ‘F’ using the code below:
Sub FindWithWildCard()
Dim rgFound As Range
Set rgFound = Worksheets("Sheet5").Range("A2:A6").Find("F*")
Debug.Print rgFound.Value & " was found in cell " & rgFound.Address
End Sub
When you run the subroutine, it prints the address of the cell with the state that begins with the letter ‘F’ to the Immediate Window:
Example #6: VBA to Find a Value With a Specific Format in a Range
You can use VBA code to find values of a particular format, for instance, bold or a specific fill color in a range.
Suppose you have the dataset below on Sheet6 of the current workbook:
You can use the code below to find the value in the red cell:
Sub FindValueWithFormat()
Dim rgFound As Range
Application.FindFormat.Clear
Application.FindFormat.Interior.Color = vbRed
Set rgFound = Worksheets("Sheet6").Range("A2:A6").Find("*", SearchFormat:=True)
Debug.Print "Found format in cell: " & rgFound.Address
Application.FindFormat.Clear
End Sub
When you run the subroutine, it prints the address of the red cell to the Immediate Window:
Note: It is good practice to clear the format before you use it, as I have done in the above example. The clearing is necessary because when you set attributes of the ‘Application.FindFormat’ property, they remain in place until you reset them.
For example, suppose you set the format to bold and then use the ‘Range.Find’ method.
Then, you set the format to font color to red and use the ‘Range.Find’ method again. The search will look for cells where the font is bold, AND the font color is red, resulting in erroneous results.
VBA to Find Multiple Values in a Range
You will often want to find multiple occurrences of the same value. You must use a loop to search and find all the values to achieve this.
Suppose you have the dataset below on Sheet7, with many occurrences of ‘Brazil.’
You can use the subroutine below to find all occurrences of ‘Brazil’ in the dataset:
Sub FindMultipleSearch()
Dim name As String: name = "Brazil"
Dim rgSearch As Range
Set rgSearch = Worksheets("Sheet7").Range("A2:A11")
Dim cell As Range
Set cell = rgSearch.Find(name)
If cell Is Nothing Then
Debug.Print "Not found"
Exit Sub
End If
Dim firstCellAddress As String
firstCellAddress = cell.Address
Do
Debug.Print cell.Value & " " & "found in: " & cell.Address
Set cell = rgSearch.FindNext(cell)
Loop While firstCellAddress <> cell.Address
End Sub
When you execute the code, it prints the addresses of all the cells containing ‘Brazil’ to the Immediate Window:
The code in this example searches for the value ‘Brazil’ within the cell range ‘A2:A11 of ‘Sheet7’ in the current workbook.
It utilizes the Find method to locate the first occurrence of ‘Brazil’ and then uses the FindNext method to find subsequent occurrences.
The code then prints the addresses of the found cells to the Immediate Window.
Also read: Count Rows using VBA in Excel
Alternative to Using ‘Range.Find’ Method
You can also use a ‘For Each Next’ loop to find values in a range.
Assume you have the dataset below on Sheet7 with multiple occurrences of ‘Brazil.’
You can use the code below to find all occurrences of ‘Brazil’ in the dataset:
Sub FindUsingLoop()
Dim cell As Range
For Each cell In Worksheets("Sheet7").Range("A1:B25")
If cell.Value = "Brazil" Then
Debug.Print "Brazil found in: " & cell.Address
End If
Next cell
End Sub
When you run the code, it prints the addresses of all cells with ‘Brazil’ in the dataset to the Immediate Window:
The code iterates through each cell in the range A1:B25 of Sheet7 in the current workbook. It checks if the value of each cell is ‘Brazil,’ and if it is, it prints the cell’s address to the Immediate Window.
Also read: Creating Named Range Using VBA
Example Application of Finding Value in Range
You can search for values in a range to, for instance, replace them with other values.
Suppose you have the dataset below on Sheet7 of the current workbook:
You can use the VBA code below to replace ‘Brazil’ with ‘USA’:
Sub FindReplaceValue()
Dim rgFound As Range
Dim firstAddress As String
With Worksheets("Sheet7").Range("A2:A11")
Set rgFound = .Find("Brazil", LookIn:=xlValues)
If Not rgFound Is Nothing Then
firstAddress = rgFound.Address
Do
rgFound.Value = "USA"
Set rgFound = .FindNext(rgFound)
Loop While Not rgFound Is Nothing
End If
End With
End Sub
When you execute the code, it replaces each ‘Brazil’ value with ‘USA’:
The code in this example searches for the value ‘Brazil’ within the cell range A2:A11 on Sheet7 of the current workbook.
If ‘Brazil’ is found, the code replaces it with ‘USA’. The code utilizes the Find method to search for the target value and the FindNext method to continue searching for subsequent occurrences until none are left.
In this tutorial, I showed you several examples of finding a value in a range using VBA. I hope you found the tutorial helpful.
Other Excel articles you may also like: