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
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:
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.
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…
Hello, Stefano! Can you please share your queries in English? Thanks!