FindNext Using VBA in Excel (2 Examples)

If you are working with Microsoft Excel’s VBA, you know how much time it can save. You may feel that VBA codes are complex to write. But once you have learned that, you can easily do that. A VBA code can do a lot of complex operations with ease. One of them is the FindNext method of VBA. In this tutorial, you will learn about FindNext and how to implement this using VBA in Excel.

Here is an overview of this article.

What is FindNext in VBA?

In VBA codes of Excel, the FindNext method comes after the Find method. It continues to find the next occurrences in a given range. It finds the next cell that equals those same conditions and returns a Range object which represents that cell. This does not change the choice or the active cell.

Syntax:

Parameters

We have to use this FindNext method with a range.

How Does FindNext Work in Excel?

We use the Find and FindNext methods to search for data with great speed. This is the prime advantage.  Using a ‘for next’ loop is also great to find data and execute actions on the data but the process is considerably slower.

First, we specified a value that we wanted to search in the spreadsheet. Then we use the Find method to find the first occurrence. After that, to find the next occurrence we use the FindNext method. It will find the next values in the given range.

Just have a look at these screenshots to have a better understanding:

Here we have a column consisting of the occurrence of some names of animals. We want to find Tiger from the column.

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

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

When we apply the FindNext method, it will select the next occurrence.

After that, it will select every other occurrence one by one.

Example 1: Find a Specific Value and Their Number of Occurrences Using the FindNext in Excel

In this section, we are going to find a specific value in a range. And we will also count how many values there are.

To insert VBA codes, first press Alt+F11 on your keyboard. Select Insert > Module. After that, a VBA editor will open.

Build the Code with FindNext Using VBA

📌 Step 1: Creating the Subprocedure

First, we are creating a sub-procedure for our VBA code.

Sub FindNext_value()
End Sub

📌 Step 2: Declaring the Range 

Now, declare our search range. Where our FindNext will take place.

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: Create 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 takes the input from the user and stores it to the search_value.

📌 Step 4: Create 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

It will take the search range from the user and store it into search_range.

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

“Application.DisplayAlerts = False” this will turn off the alerts if you don’t enter any range in the box. It won’t work until you give one.

📌 Step 5: Declare 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 will take the input from the input box with the “Application.InputBox” function.

📌 Step 6: Conduct 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)” it will find the first occurrence of your search value.

📌 Step 7: Create 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 will use the “occur” variable for the loop.

📌 Step 8: Create 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: Show Message on How Many Times Your Values were Found

We have added an extra If segment to check whether you give a range 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 will show the number of occurrences using a MsgBox.

Run the Code

Now, we are going to implement this method on the previous dataset. We are going to find Tiger from this dataset.

📌 Steps  

① Press Alt+F8 on your keyboard for the macro.

② Select FindNext_values.

③ Click on Run

④ Now, type “Tiger” to search. Click Ok.

⑤ Select the range of cells  B5:B11 of your dataset. Click Ok.

⑥ In the following screenshots, you can see the position of your values.

As you can see the first occurrence on Cell B6

After that, the second occurrence on Cell B8

Finally, you can see the last occurrence on Cell B9

In the end, you can see the total number of occurrences of the value Tiger.

Read More: Excel VBA to Find Value in Column

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

Now, in this section, you will learn the VBA code on how to find empty cells with FIndNext in Excel. Also, fill these blank cells with a value.

There will be a lot of similarities like the previous example. So, make sure you observe the first example attentively.

To insert VBA codes, first press Alt+F11 on your keyboard. Select Insert > Module. After that, a VBA editor will open.

Build the Code with FindNext Using VBA

📌 Step 1: Creating Subprocedure

Sub FindNext_empty()
End Sub

📌 Step 2: Create 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 are taking the search value from the user and storing it in a variable.

We are using the If condition to check whether the user press Cancel or not. If the user press Cancel, it will end the procedure,

📌 Step 3: Create InputBox to Take 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

Here, we are taking the search range from the user. If there is no search range, it won’t go to the next steps. If the user press Cancel, it will exit the procedure.

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

📌 Step 4: Create 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 message to give a value. After that, it will again ask for value.

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

📌 Step 5: Conduct First Search Using 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

Here, using the Find method, we will search for the first blank cell in the range.

📌 Step 6: Create Loop to Find Every Blank CellThis Takes and Fill Them 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 are using the counter variable to count the number of replacements. This loop will search every value using the FindNext method in the search range. Then it will replace them with your fill_value.

📌 Step 7: Show 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 

Now, we are going to implement this code on the following dataset:

Here, we have some persons’ names and their marital status. You can see there are some empty cells. Now, we are going to fill these blank cells with the value “Unmarried”.

📌 Steps

① First, press Alt+F8 on your keyboard for the do the acro dialog box.

② Then, select FindNext_empty_value.

③ Click on Run

④ Keep the field empty as we are searching for blank cells. After that click on OK.

⑤ Then, select your search range to find empty cells. Click on OK after selecting.

⑥ In the “Fill the Blanks” dialog box, type “Unmarried” to fill the blanks with this value.

⑦  Click on Ok.

Finally, you can see,  we have replaced all the empty cells with the FindNext method using VBA in Excel.

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

Download this practice workbook

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the FindNext method using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

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