VBA FIND NEXT

What is Excel VBA Find Next?

In Excel VBA (Visual Basic for Applications), the “Find Next” functionality refers to the capability to locate the next occurrence of a specified value or criteria within a range of cells. This is often used in macros or scripts to automate tasks that involve searching for and manipulating data in Excel.

The basic idea is to use the VBA Find method to search for a specific value, and then use the VBA Find Next cell with value method to find the next occurrence of the same value within the specified range. Consider the following example:

Create a subroutine to search for “Cat” in the list of animals given above. Then find the address of the next occurance of “Cat” and print it.

The value “Cat” is available in cell “A3” and cell “A9”. Using the VBA Find function, the cat value is found at cell “A3”, then with VBA Find Next row with data, it is updated to cell “A9” before printing it.

What is Excel VBA Find Next?

The FindNext method in VBA is used in conjunction with the Find method for searching and locating occurrences of a specific value or criteria within a range.
After using the Find method to find the first occurrence, FindNext is employed to find the next occurrence within the specified range.
It is often used in a loop to iterate through all occurrences of the specified value within the range.
The loop typically terminates when the FindNext method returns to the first found cell, indicating that all occurrences have been processed.
To use FindNext, the reference to the current found cell needs to be updated to the next found cell in each iteration of the loop.
Updating specific cells, copying entire rows, or performing other actions on all occurrences of a particular value within a range.

How to Use Find Next in Excel VBA?

Follow the steps to learn how to use the VBA Find Next cell with value.

Once you open the Excel Workbook, in the title bar, choose the “Developer” icon and click on it.

After you click the Developer icon, select “Visual Basic”. This opens the VBA Editor where you can start coding.

In the Editor, click the “Insert” button and select the “Module” option.
Name the subroutine to use the VBA Find Next cell with value for the given table.
Set the range in which the subroutine will be working.
Find the string “Orange” in the table using the VBA Find function and store it in a variable of the Range datatype.
Check if the variable is not empty then assign its address to another variable.
Initialize a Do loop where the address of the variable is printed in the immediate tab using Debug.Print.
For updating the range to find multiple instances of the string in question, initialize the VBA Find Next row with data.
If there is no other instance of the string available, exit the Do loop with an If condition and set the default address found earlier.
Continue the Do loop all instances of the string are found.

Code:

Sub FindNextExample()
Dim rng As Range
Set rng = Worksheets(“Sheet2”).Range(“A2:A10”)
Dim firstFound As Range
Set firstFound = rng.Find(What:=”Orange”)
If Not firstFound Is Nothing Then
firstAddress = firstFound.Address
Do
Debug.Print firstFound.Address
Dim nextFound As Range
Set nextFound = rng.FindNext(After:=firstFound)
If nextFound Is Nothing Then Exit Do
Set firstFound = nextFound
Loop While firstFound.Address <> firstAddress
Else
MsgBox “TargetValue not found in the specified range.”
End If
End Sub
Click “F5” or the “Run” icon on the activity bar in Excel VBA to run the subroutine.

Now you have successfully learned how to use the VBA FindNext function.

Examples of Find Next Method in Excel VBA

See the different ways in which you can use VBA FindNext for your purposes.

Example #1

Suppose you have a set of numbers. You want to find all available instances of a particular number and color the background differently to highlight them. You can use the VBA Find Next function to do so.

Step 1: Define a subroutine to find the number of instances of ‘2’ and highlight them.

Step 2: Using the VBA With function, you can concatenate multiple functions into a range in a neat way. Here, the range is defined.

Step 3: Find the range of the value ‘2’ in the range of the table defined above.

Step 4: Check if the address range of the number ‘2’ is not empty using an If condition and assign its address to a new variable.

Step 5: Define a Do loop which loops the following functions.

If the number 2 is found in the cell, the interior color changes to pink and the font color changes to white. Then the address is updated using the VBA Find Next function.

Step 6: Continue the do loop when it is not the first value and the address isn’t empty.

Step 7: End the VBA With function.

Code:

Sub ExampleWithLoop()
With Worksheets(“Sheet3”).Range(“a1:a10”)
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Color = RGB(200, 0, 128)
c.Font.Color = vbWhite
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

Step 8: Press “F5” to run the VBA code. The output is displayed as shown below:

Example #2

Suppose you have a list of vegetables and their prices. You want to display the values of any vegetable by concatenating them into a list. You can use VBA Find Next row with data and concatenate the target prices and display them.

Step 1: Define a subroutine to concatenate values of similar target names and display their values.

Step 2: Define the range to be searched and store the range in a variable.

Step 3: Define the search value to be searched using the VBA Find function.

Step 4: Define an empty string variable to store the values from “Carrot”.

Step 5: Search for the address of the value “Carrot” within the range using the VBA Find function.

Step 6: Check whether the searched address isn’t empty using an If-condition and assign the address of the first instance of the value found.

Step 7: Start a Do loop and inside the loop, assign the values to be concatenated from the second column.

Step 8: Call VBA Find Next to find other instances of “Carrot” in the range.

Step 9: Continue running the loop until there are no more instances left. Then exit the Do loop.

Step 10: Print the concatenated values. To print and delete the extra space and comma, you need to subtract 2 from the length of the concatenated values in the VBA Left function of concatenated values with respect to it’s length.

Step 11: Declare an Else condition if the value does not exist.

Code:

Sub ConcatenateValuesForTarget()
Dim searchRange As Range
Set searchRange = Worksheets(“Sheet4”).Columns(“A”)
Dim targetValue As String
targetValue = “Carrot”
Dim concatenatedValues As String
concatenatedValues = “”
Dim foundCell As Range
Set foundCell = searchRange.Find(What:=targetValue, LookIn:=xlValues)
If Not foundCell Is Nothing Then
Dim firstFoundCell As Range
Set firstFoundCell = foundCell
Do
concatenatedValues = concatenatedValues & Worksheets(“Sheet4”).Cells(foundCell.Row, “B”).value & “, “
Set foundCell = searchRange.FindNext(After:=foundCell)
If foundCell Is Nothing Or foundCell.Address = firstFoundCell.Address Then Exit Do
Loop
MsgBox “Concatenated values for ” & targetValue & “: ” & Left(concatenatedValues, Len(concatenatedValues) – 2)
Else
MsgBox targetValue & ” not found in the specified range.”
End If
End Sub

Step 12: Click the green arrow button when you want to run the code. It will pop up a Message Box once run.

VBA Find Next in Excel - Example 2 - Step 12

Example #3

Consider an example where, based on the names, you need to change their work status to “Updated”. If a user inputs the name of the employee, the status column should change to “Updated” in the last column. You can use VBA FindNext to find similar names.

Step 1: Define a subroutine to change the status of an employee at a stretch if multiple instances of their names are there in the list.

Step 2: Get the employee name from the user with the VBA InputBox function.

Step 3: Find the range of the employee name using the VBA find function and then store it in a variable.

Step 4: Check whether the range variable holding in the target employee isn’t empty. If it isn’t, define a VBA Range variable to store the address where the target employee is found at. This address will be updated every time the VBA FindNext function is called in the Do Loop.

Step 5: Initialize a Do Loop and assign the 4th column of the row where the employee name was found.

Step 6: Assign the address of the next instance when the employee comes in the list to a new variable.

Step 7: If there are no instances of the employee’s name left, exit the Do Loop.

Step 8: Continue the loop when the current address of the employee isn’t equal to the first instance.

Step 9: Print the concatenated values in a message box.

Step 10: Define an Else condition in case the Employee name does not exist.

Code:

Sub UpdateStatusUsingFindNext()
Dim searchRange As Range
Set searchRange = Worksheets(“Sheet5”).Columns(“A”)
Dim targetName As String
targetName = InputBox(“Enter the target name”)
Dim foundCell As Range
Set foundCell = searchRange.Find(What:=targetName, LookIn:=xlValues)
If Not foundCell Is Nothing Then
Dim firstFound As Range
Set firstFound = foundCell
Do
Worksheets(“Sheet5”).Cells(foundCell.Row, “D”).value = “Updated”
Dim nextFound As Range
Set nextFound = searchRange.FindNext(After:=foundCell)
Set foundCell = nextFound
If foundCell Is Nothing Then Exit Do
Loop While foundCell.Address <> firstFound.Address
MsgBox “Status updated for all occurrences of ” & targetName
Else
MsgBox targetName & ” not found in the specified range.”
End If
End Sub

Step 11: Run the above subroutine and go to “Sheet5” to see the changes. There will be an InputBox in VBA where you’ll need to enter the name.

Excel VBA Find Next - Example 3 - Step 11a

Excel VBA Find Next - Example 3 - Step 11b

Excel VBA Find Next - Example 3 - Step 11c

Important Things to Note

Always use FindNext after using the Find method to continue searching for the next occurrence.
To get the VBA Find Next empty column employ FindNext in a loop to iterate through all occurrences of the specified value within the range.
Update the reference to the found cell with each iteration of the loop to ensure correct navigation through the range.
Don’t assume that the loop will always continue indefinitely. Include a termination condition based on your specific requirements.
Avoid modifying the search range (e.g., inserting or deleting rows in VBA) while actively using Find and FindNext within the same loop.
Don’t assume that found occurrences will always be consecutive. FindNext continues the search from the current cell, not necessarily from the top of the range.

Frequently Asked Questions (FAQs)

1. Can VBA Find Next be used with wildcards?

Yes, VBA FindNext can be used with wildcards to search for patterns or partial matches within a specified range. Use the * wildcard for multiple characters and ? for a single character.

2. How to handle errors when using VBA Find Next?

Use On Error Resume Next before the Find and FindNext operations, then check for errors using If Err.Number <> 0 for example if there is a case of the VBA Find Next empty row. If an error occurs, handle it appropriately (e.g., exit the loop). Finally, reset error handling with On Error GoTo 0.

3. Can VBA Find Next be used with non-contiguous ranges?

No, VBA FindNext is designed to work with contiguous ranges. To search non-contiguous ranges, consider using a loop with separate Find operations for each range.

4. What is the difference between Find and Find Next in VBA?

Download Template

This article must be helpful to understand the VBA Find Next, with its features and examples. You can download the template here to use it instantly.

VBA Find Next Download

VBA FIND NEXT