VBA to Find Value in Range in Excel

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:

ArgumentDescription
WhatThe data to search for.
AfterThe 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.
LookInIt can be one of the following constants: xlValues, xlFormulas, xlComments, or xlCommentsThreaded.
LookAtIt can be one of the following constants: xlPart or xlWhole.
SearchOrderIt can be one of the following constants: xlByColumns or xlByRows.
SearchDirectionIt can be one of the following constants: xlPrevious or xlNext.
MatchCaseThe default value is ‘False’ to make the search case-insensitive. Use ‘True’ to make the search case-sensitive.
MatchByteIt 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. 
SearchFormatThe 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. 

List of countries in Excel

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:

Excel finds the value in the range with VBA

Example #2: VBA to Find the Second Occurrence of a Value in a Range

Assume you have the dataset below on the active sheet:

Dataset with repetitions

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:

Finding the second occurrence of the value using VBA

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.’

Dataset without repetition

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.

Value in 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:

Find value in a formula using VBA

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:

threaded comments in cells in Excel

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:

Finding value in comments using VBA

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:

Dataset in Excel state names

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:

VBA to Find a Value in a Range Using Wildcard Characters

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:

Dataset with formatting

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:

VBA to Find a Value With a Specific Format in a Range

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.’

Dataset with many occurrences of values

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:

VBA to Find Multiple Values in a Range

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.’

Dataset with many occurrences of values

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:

VBA Loop to Find Multiple Values in a Range

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:

Dataset with many occurrences of values

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’:

Dataset with many occurrences of values

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment