FindNext Using VBA in Excel (2 Examples)

Here is an overview of this article:

What is FindNext in VBA?

In VBA code, the FindNext method follows the Find method. It finds the next cell that equals the same conditions and returns a Range object representing that cell. This does not change the current selection or the active cell.

Syntax:

Parameters

How Does FindNext Work in Excel?

We use the Find and FindNext methods to search for data quickly. This is the prime advantage over a  ‘For Next’ loop, which is considerably slower.

The process is broadly speaking as follows: We specify a value to search in the spreadsheet. We use the Find method to find the first occurrence. To find the next occurrence(s) we use the FindNext method.

To illustrate, consider the following column of animal names:

Supposes we want to find Tiger in the column.

First, we select the range where we want to apply our methods.

Then we apply the Find method to find the first occurrence.

Then we apply the FindNext method to select the next occurrence.

It will then select every other occurrence sequentially.

Example 1 – Find a Specific Value and the Number of Occurrences Using the FindNext Method

Let’s write some VBA code to find a specific value in a range and count how many values there are using FindNext.

• To insert VBA codes, press Alt+F11 on the keyboard.
• Select Insert > Module.

A VBA editor will open.

Build the Code

Step 1 – Creating the Subprocedure

Sub FindNext_value()
End Sub

Step 2 – Declaring the Search Range 

Sub FindNext_value()
Dim search_range As Range
End Sub

Step 3 – Declaring the Variable for the Search Value

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
End Sub

Step 4 – Creating an InputBox to Take the Search Value from the User

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
search_value = InputBox("Which Value You Want to Find ?")
End Sub

This input box takes the input from the user and stores it in search_value.

Step 4 – Creating an InputBox to Take the Search Range from the User

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
End Sub

This input box takes the search range from the user and stores it in search_range.

“Type:=8” means this input type is cell.

“Application.DisplayAlerts = False” this turns off the alerts if no range is entered in the box. It won’t work until a range is provided.

Step 5 – Declaring the Variable to Store the Search Results

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
Dim FindRng As Range
End Sub

We take the input from the input box with the “Application.InputBox” function.

Step 6 – Conducting the First Search Using Find Method

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
Dim FindRng As Range
Set FindRng = search_range.Find(What:=search_value)
End Sub

“search_range.Find(What:=search_value)” will find the first occurrence of the search_value.

Step 7: Creating Variables for the Search Values Location

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
counter = 0
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
Dim FindRng As Range
Set FindRng = search_range.Find(What:=search_value)
Dim occur As String
occur = FindRng.Address
End Sub

We use the “occur” variable for the loop.

Step 8 – Creating a Loop to Search the Whole Column and Show the Locations

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
counter = 0
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
Dim FindRng As Range
Set FindRng = search_range.Find(What:=search_value)
Dim occur As String
occur = FindRng.Address
Do
   counter = counter + 1
   MsgBox "Your Value was found on cell: " & FindRng.Address
  Set FindRng = search_range.FindNext(FindRng)
Loop While occur <> FindRng.Address
End Sub

This Do-while loop will search for the value until the last occurrence. For every occurrence, it will show the location of the search results and count the occurrences.

Step 9- Showing a Message on How Many Times Your Values were Found

We added an extra If segment to check whether a range is given or not.

Sub FindNext_value()
Dim search_range As Range
Dim search_value As Variant
counter = 0
search_value = InputBox("Which Value You Want to Find ?")
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.InputBox( _
Title:="Search Range", _
Prompt:="Select the Range of Cells", _
Type:=8)
On Error GoTo 0
If search_range Is Nothing Then
 MsgBox "Give a Range Please !!"
 Exit Sub
End If
Dim FindRng As Range
Set FindRng = search_range.Find(What:=search_value)
Dim occur As String
occur = FindRng.Address
Do
 counter = counter + 1
 MsgBox "Your Value was found on cell: " & 
 FindRng.Address
 Set FindRng = search_range.FindNext(FindRng)
Loop While occur <> FindRng.Address
MsgBox "Search is over. We found your values " & counter & " times"
End Sub

Finally, we display the number of occurrences using a MsgBox.

• Save the code.

Run the Code

Let’s implement this method on our dataset to find Tiger.

Steps: 

• Press Alt+F8 to open the Macros window.
• Select FindNext_values.

• Click on Run.
• Type “Tiger” to search.
• Click OK.

• Select the range of cells B5:B11.
• Click OK.

In the following screenshots are the position of our values.

The first occurrence is on Cell B6.

The second occurrence is on Cell B8.

The last occurrence is on Cell B9.

The total number of occurrences of the value Tiger is shown.

Read More: Excel VBA to Find Value in Column

Example 2 – Find Blank Cells and Fill Them with Data Using FindNext

Now let’s use FindNext in VBA code to find empty cells, and fill them with a value.

It is a similar process to the the previous example.

• To insert VBA codes, press Alt+F11 on the keyboard.
• Select Insert > Module.

A VBA editor will open.

Build the Code with FindNext Using VBA

Step 1 – Creating a Subprocedure

Sub FindNext_empty()
End Sub

Step 2 – Creating an InputBox to Take Input from the User 

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
End Sub

We take the search value from the user and store it in a variable.

We use the If condition to check whether the user presses Cancel or not. If the user presses Cancel, it will end the procedure,

Step 3 – Creating an InputBox to Take the Search Range from the User

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
 Exit Sub
End If
End Sub

If there is no search range, the procedure will stop. If the user presses Cancel, the procedure will exit.

“Application.DisplayAlerts = False” will disable all the alerts if the user does not provide any values.

Step 4 – Creating Another InputBox for Blank Cells to Fill Them Up

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
 Exit Sub
End If
Dim fill_value As Variant
fill:
fill_value = InputBox("Enter Your Data to Fill the Blanks Cells", "Fill the Blanks")
If StrPtr(fill_value) = o Then
 Exit Sub
ElseIf fill_value = vbNullString Then
 MsgBox "Please GIve a Value to Fill the Blank Cells"
 GoTo fill
End If
End Sub

Similarly, we will take a value to fill the empty cells. If the user gives no value, it will show a prompt to provide a value, before again requesting a value.

If the user press Cancel, it will end the procedure.

Step 5 – Conducting the First Search Using the Find Method

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
 Exit Sub
End If
Dim fill_value As Variant
fill:
fill_value = InputBox("Enter Your Data to Fill the Blanks Cells", "Fill the Blanks")
If StrPtr(fill_value) = o Then
 Exit Sub
ElseIf fill_value = vbNullString Then
 MsgBox "Please GIve a Value to Fill the Blank Cells"
 GoTo fill
End If
Dim FindRng As Range
Set FindRng = search_range.Find(What:=search_value)
End Sub

Using the Find method, we search for the first blank cell in the range.

Step 6 – Creating a Loop to Find Every Blank Cell and Fill Each with a Value

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
 Exit Sub
End If
Dim fill_value As Variant
fill:
fill_value = InputBox("Enter Your Data to Fill the Blanks Cells", "Fill the Blanks")
If StrPtr(fill_value) = o Then
 Exit Sub
ElseIf fill_value = vbNullString Then
 MsgBox "Please GIve a Value to Fill the Blank Cells"
 GoTo fill
End If
Dim FindRng As Range
Set FindRng = search_range.Find(what:=search_value)
counter = 0
Do
 counter = counter + 1
 FindRng.value = fill_value
 Set FindRng = search_range.FindNext(FindRng)
Loop While Not FindRng Is Nothing
End Sub

We use the counter variable to count the number of replacements. This loop will search for all occurrences of the search value in the search range using the FindNext method, then replace them with the fill_value.

Step 7 – Showing the Number of Replacements with a Messagebox

Sub FindNext_empty_value()
Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")
If StrPtr(search_value) = 0 Then
 Exit Sub
End If
Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If search_range Is Nothing Then
 Exit Sub
End If
Dim fill_value As Variant
fill:
fill_value = InputBox("Enter Your Data to Fill the Blanks Cells", "Fill the Blanks")
If StrPtr(fill_value) = o Then
 Exit Sub
ElseIf fill_value = vbNullString Then
 MsgBox "Please GIve a Value to Fill the Blank Cells"
 GoTo fill
End If
Dim FindRng As Range
Set FindRng = search_range.Find(what:=search_value)
counter = 0
Do
 counter = counter + 1
 FindRng.value = fill_value
 Set FindRng = search_range.FindNext(FindRng)
Loop While Not FindRng Is Nothing
MsgBox "Total " & counter & " empty cells were replaced with: " & fill_value
End Sub

Run the Code 

After saving the code, let’s implement it on the following dataset:

Here, we have some persons’ names and their marital status, including some empty cells. We’ll fill these blank cells with the value “Unmarried”.

Steps:

• Press Alt+F8 to open the Macro dialog box.
• Select FindNext_empty_value.
• Click on Run.

• Keep the field empty as we are searching for blank cells.
• Click on OK.

• Select the search range to find empty cells.
• Click on OK.

• In the “Fill the Blanks” dialog box, type “Unmarried”.

• Click on OK.

All the empty cells are replaced using the FindNext VBA method.

Read More: Excel VBA: Find the Next Empty Cell in Range 

Things to Remember

✎ The Find method can find just one value at a time.

✎ The FindNext will find the next value after the Find method has found one.

Download Practice Workbook

Related Articles

• How to Find Exact Match Using VBA in Excel
• Excel VBA to Find Matching Value in Column
• How to Find Last Row Using Excel VBA
• Find Last Row with Data in a Range Using Excel VBA Macros
• Excel VBA to Find Multiple Values in Range