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.


Download Practice Workbook

Download this practice workbook


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:

expression.FindNext(After)

Parameters

Name Requirement  Type Return Description
After optional Variant Range  After which cell do you want to find next. This compares to the location of the active cell when a search is done from the user interface. Remember, After must be a single cell in the range.

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:

dataset for vba findnext in excel

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.

search range for vba findnext excel

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

result of first find method in excel vba

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

first findnext method search result in excel

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

result of the method using vba

Read more: How to Use the Find Function in VBA


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.

insert vba module

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.

dataset for vba findnext in excel

📌 Steps  

Press Alt+F8 on your keyboard for the macro.

Select FindNext_values.

vba macro dialog box in excel

Click on Run

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

enter value to find in excel

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

select range of cells for vba findnext method

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

As you can see the first occurrence on Cell B6

first result of vba findnext

After that, the second occurrence on Cell B8

second occurrence of tiger

Finally, you can see the last occurrence on Cell B9

final occurrence of tiger vba findnext

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

total count of the desired value


Similar Readings:


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.

insert vba module

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:

dataset for vba findnext in excel

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

macro dialog box for findnext method in excel

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

keep the field empty for findnext method

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

select range of cells for findnext method vba

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

type the value to fill the empty cells with this in excel

 Click on Ok.

result of vba findnext in excel

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


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


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. Don’t forget to check our website exceldemy.com for various Excel-related problems and solutions.


Related Articles

Tags:

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. Grazie! Potresti per favore implementare in FindNext:
    -indicare la riga invece della cella trovata?
    -si potrebbe avere colore della(e) cella(e) trovate in rosso o giallo o…

Leave a reply

ExcelDemy
Logo