WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Find Next

VBA Find Next

Excel VBA Find Next

Like in excel when we press CTRL + F a wizard box pops up which allows us to search a value in the given worksheet and once the value is found we click on find next to find the other similar value, as it is a worksheet feature we can also use it in VBA as Application property method as application.findnext for the same purposes.

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 have discussed the “Find” method in VBA, and it is not complex at all but finding all the repetitive occurrences is possible only with the “Find Next” method in excel VBA.

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

vba find next

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 returns back to the original cell where we have started the search.

This is the advanced version of the “Find” method, which searches only one time the mentioned value in the mentioned range.

Below is the syntax of the FIND NEXT method in Excel VBA.

Find Next Formula in VBA

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

Examples of Find Next Method in Excel VBA

Below are the Examples of finding the next method in excel VBA.

For example, look at the below data.

You can download this VBA Find Next Excel Template here – VBA Find Next Excel Template

VBA Find Next - Example 1

Step#1 – In this data, we need to find the city name “Bangalore.” Let’s start the subprocedure in the basic visual editor.

Code:

Sub RangeNext_Example()

End Sub

VBA Find Next - Example 1-1

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

Code:

Sub RangeNext_Example()

 Dim Rng As Range

End Sub

VBA Find Next - Example 1-2

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

Code:

Sub RangeNext_Example()

 Dim Rng As Range
 Set Rng = Range("A2:A12")

End Sub

VBA Find Next - Example 1-3

Since our data of the city list is there in the range of cells from A2 to A11 in this range, only we are going to 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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

Code:

Sub RangeNext_Example()

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

End Sub

VBA Find Next - Example 1-4

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

Code:

Sub RangeNext_Example()

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

End Sub

VBA Find Next - Example 1-5

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

Code:

Sub RangeNext_Example()

 Dim Rng As Range
 Dim CellAdderess As String

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

End Sub

VBA Find Next - Example 1-6

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

Code:

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

VBA Find Next - Example 1-7

Note: RNG. Address because RNG will be having the reference for 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

VBA Find Next - Example 1-8

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

VBA Find Next - Example 1-9

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

Step#10 – We need to reference the range object variable but by using the FIND NEXT method in excel VBA.

Code:

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

VBA Find Next - Example 1-10

As you can see above, we have used the VBA FIND NEXT 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.

Code:

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

VBA Find Next - Example 1-11

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

VBA Find Next - Example 1-12

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

VBA Find Next - Example 1-13

The second value found in A7 cell, press Ok to continue.

VBA Find Next (Using Loop)

It will exit the VBA subprocedure, but we are one more to be found in cell A10. When the values are to be found in more than one cell, then it is a better idea 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.

Code:

Sub RangeNext_Example1()

 Dim Rng As Range
 Dim FindRng As Range

End Sub

Example 2

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

Code:

Sub RangeNext_Example1()

 Dim Rng As Range
 Dim FindRng As Range

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

End Sub

Example 2-1

Step#16 – For the second variable, set the reference by using the FIND VBA function.

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

Example 2-3

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

Code:

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

Example 2-4

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

Code:

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

Example 2-5

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

Code:

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

Example 2-6

Inside the loop, mention the message box and VBA FIND NEXT method.

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

Code:

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 as “Search is Over” in the new message box.

Example 2-7

Things to Remember

  • FIND method can find only one value at a time.
  • FIND NEXT in excel VBA can find the next value from the already found value cell.
  • Use the Do While loop to loop through all the cells in the range.

Recommended Articles

This has been a guide to VBA FIND NEXT. 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 VBA
  • VBA ListObjects
  • CDEC in VBA
  • VBA Login User Form
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Find Next Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More