Excel VBA Find Next

Finding the specific value in the mentioned range is fine, but what if the requirement is to find the value with multiple occurrences? In one of the earlier articles, we discussed the “Find” method in VBA. It is not complex, but finding all the repetitive occurrences is possible only with the “FindNext” method in Excel VBA.

This article will show you how to use this “Find Next” in Excel VBA.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Find Next (wallstreetmojo.com)

What is Find Next in Excel VBA?

As the word says, “Find Next” means from the found cell, keep searching for the next value until it returns to the original cell where we started the search.

The advanced version of the “Find” method searches only once the mentioned value is in the range.

Below is the syntax of the FINDNEXT method in Excel VBA.

After: It is the word that we are searching for.

Examples of Find Next Method in Excel VBA

Below are examples of finding the next method in Excel VBA.

Look at the below data.

Step #1 – In this data, we need to find the city name “Bangalore.” Let’s start the sub procedure in the visual basic editorVisual EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more.

Code:

Sub RangeNext_Example()

End Sub

Step #2 – First, declare the variable as a “Range” object.

Sub RangeNext_Example()

Dim Rng As Range

End Sub

Step #3 – Set the reference for the object variable as “Range(“A2: A11”).

Sub RangeNext_Example()

Dim Rng As Range Set Rng = Range(“A2:A12”)

End Sub

Since our data of the city list is in the range of cells from A2 to A11, only we will search for the city “Bangalore.”

Since we set the range reference to the variable “Rng,” we use this variable instead of using RANGE(“A2: A11”) every time.

Step #4 – Use the RNG variable and open the Find method.

Sub RangeNext_Example()

Dim Rng As Range Set Rng = Range(“A2:A12”) Rng.Find

End Sub

Step #5 – The first argument of the FIND method is “What,” i.e., what we are trying to search in the mentioned range, so the value we are searching is “Bangalore.”

Sub RangeNext_Example()

Dim Rng As Range Set Rng = Range(“A2:A12”) Rng.Find What:=“Bangalore”

End Sub

Step #6 – To show which cell we found this value in, declare one more variable as a string.

Sub RangeNext_Example()

Dim Rng As Range Dim CellAdderess As String

Set Rng = Range(“A2:A12”) Rng.Find What:=“Bangalore”

End Sub

Step #7 – For this variable, assign the found cell address.

Sub RangeNext_Example()

Dim Rng As Range Dim CellAdderess As String

Set Rng = Range(“A2:A12”).Find(What:=“Bangalore”) Rng.Find What:=“Bangalore”

CellAddress = Rng.Address

End Sub

Note: RNG. Address because RNG will have the reference for the found value cell.

Step #8 – Now show the assigned cell address variable result in the message box in VBA.

Sub RangeNext_Example()

Dim Rng As Range Dim CellAdderess As String

Set Rng = Range(“A2:A12”).Find(What:=“Bangalore”) Rng.Find What:=“Bangalore”

CellAddress = Rng.Address MsgBox CellAddress

End Sub

Step #9 – Run the code and see what we get here.

So we have found the value “Bangalore” in cell A5. However, we can find only one cell with the Find method, so instead of FIND, we need to use FINDNEXT in Excel VBA.

Step #10 – We need to reference the range object variable using the FINDNEXT method in Excel VBA.

Sub RangeNext_Example()

Dim Rng As Range Dim CellAdderess As String Set Rng = Range(“A2:A12”).Find(What:=“Bangalore”) Rng.Find What:=“Bangalore”

CellAddress = Rng.Address MsgBox CellAddress

Set Rng = Range(“A2:A12”).FindNext(Rng)

End Sub

As you can see above, we have used the VBA FINDNEXT method, but inside the function, we have used a range object variable name.

Step #11 – Now again, assign the cell address and show the address in the message box.

Sub RangeNext_Example()

Dim Rng As Range Dim CellAdderess As String Set Rng = Range(“A2:A12”).Find(What:=“Bangalore”) Rng.Find What:=“Bangalore”

CellAddress = Rng.Address MsgBox CellAddress

Set Rng = Range(“A2:A12”).FindNext(Rng) CellAddress = Rng.Address MsgBox CellAddress

End Sub

Step #12 – Run the macro and see what we get in the first message box.

Step #13 – The first message box shows the value “Bangalore” found in cell A5. Click on the “OK” button to see the next found value.

The second value found is in the A7 cell. Press “OK” to continue.

VBA Find Next (Using Loop)

It will exit the VBA subprocedureVBA SubprocedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more, but we are one more to be found in cell A10. When we find the values in more than one cell, it is better to use loops.

In this case, too, we have value “Bangalore” in more than one cell, so we need to include loops here.

Step #14 – First, declare two variables as the range.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

End Sub

Step #15 – Set the reference for the first variable, as shown below.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

Set Rng = Range(“A2:A11”).Find(What:=“Bangalore”)

End Sub

Step #16 – Set the reference using the FIND VBA functionFIND VBA FunctionVBA Find gives the exact match of the argument. It takes three arguments, one is what to find, where to find and where to look at. If any parameter is missing, it takes existing value as that parameter.read more for the second variable.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

Set Rng = Range(“A2:A11”).Find(What:=“Bangalore”) Set FindRng = Rng.FindNext(“Bangalore”)

End Sub

Step #17 – Before we start searching for the value, we need to identify from which cell we start the search, which declares the variable as a string.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

Set Rng = Range(“A2:A11”).Find(What:=“Bangalore”) Set FindRng = Rng.FindNext(“Bangalore”)

Dim FirstCell As String FirstCell = Rng.Address

End Sub

Step #18 – For this variable, assign the first cell address.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

Set Rng = Range(“A2:A11”) Set FindRng = Rng.Find(What:=“Bangalore”)

Dim FirstCell As String FirstCell = Rng.Address

End Sub

Step #19 – We need to include the “Do While” loop to loop through all the cells and find the searching value.

Sub RangeNext_Example1()

Dim Rng As Range Dim FindRng As Range

Set Rng = Range(“A2:A11”).Find(What:=“Bangalore”) Set FindRng = Rng.FindNext(“Bangalore”)

Dim FirstCell As String FirstCell = Rng.Address

Do Loop While FirstCell <> Cell.Address

End Sub

Inside the loop, mention the message box and VBA FINDNEXT method.

Step #20 – Below is the complete code for you.

Sub FindNext_Example()

Dim FindValue As String FindValue = “Bangalore”

Dim Rng As Range Set Rng = Range(“A2:A11”)

Dim FindRng As Range Set FindRng = Rng.Find(What:=FindValue)

Dim FirstCell As String FirstCell = FindRng.Address

Do MsgBox FindRng.Address Set FindRng = Rng.FindNext(FindRng) Loop While FirstCell <> FindRng.Address

MsgBox “Search is over”

End Sub

Step #21 – This will keep showing all the matching cell addresses, and in the end, it will show the message “Search is Over” in the new message box.

Things to Remember

  • The FIND method can find only one value at a time.The FINDNEXT method in Excel VBA can find the next value from the already found value cell.We must use the Do While loop to loop through all the cells in the range.

This article has been a guide to VBA FINDNEXT. Here, we discuss how to find the specific value using Excel VBA FindNext function, along with examples. You can learn more about VBA functions from the following articles: –

  • For Next Loop in VBAVBA ListObjectsCDEC in VBAVBA Login User Form