VBA FIND NÆSTE - Hvordan bruges FindNext-funktion i Excel VBA?

Indholdsfortegnelse

Excel VBA Find næste

Som i excel, når vi trykker på CTRL + F, vises en guidenboks, der giver os mulighed for at søge efter en værdi i det givne regneark, og når værdien er fundet, klikker vi på find ved siden af ​​for at finde den anden lignende værdi, da det er en regnearksfunktion, vi kan også bruge den i VBA som Application property-metode som application.findnext til samme formål.

At finde den specifikke værdi i det nævnte interval er fint, men hvad nu hvis kravet er at finde værdien med flere forekomster. I en af ​​de tidligere artikler har vi diskuteret "Find" -metoden i VBA, og den er slet ikke kompleks, men det er kun muligt at finde alle de gentagne forekomster med "Find Next" -metoden i excel VBA.

I denne artikel vil vi vise dig, hvordan du bruger denne "Find næste" i Excel VBA.

Hvad er Find næste i Excel VBA?

Som ordet siger betyder "Find næste", at den fundne celle fortsætter med at søge efter den næste værdi, indtil den vender tilbage til den oprindelige celle, hvor vi har startet søgningen.

Dette er den avancerede version af "Find" -metoden, som kun søger den nævnte værdi én gang i det nævnte interval.

Nedenfor er syntaksen for FIND NÆSTE-metoden i Excel VBA.

Efter: Det er det ord, vi leder efter.

Eksempler på Find næste metode i Excel VBA

Nedenfor er eksemplerne på at finde den næste metode i excel VBA.

Se f.eks. Nedenstående data.

Trin # 1 - I disse data skal vi finde bynavnet "Bangalore." Lad os starte delproceduren i den grundlæggende visuelle editor.

Kode:

Sub RangeNext_Example () Afslut sub

Trin # 2 - Forklar først variablen som "Range" -objekt.

Kode:

Sub RangeNext_Example () Dim Rng Som Range End Sub

Trin # 3 - Indstil referencen for objektvariablen som "Område (" A2: A11 ").

Kode:

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

Da vores data om bylisten er der inden for cellerne fra A2 til A11 i dette interval, er det kun vi, der skal søge efter byen "Bangalore."

Da vi indstiller områdereferencen til variablen “Rng”, bruger vi denne variabel i stedet for at bruge RANGE (“A2: A11”) hver gang.

Trin # 4 - Brug RNG-variablen og åbn Find-metoden.

Kode:

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

Trin # 5 - Det første argument i FIND-metoden er "Hvad", dvs. hvad vi prøver at søge i det nævnte interval, så den værdi, vi søger, er "Bangalore."

Kode:

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

Trin # 6 - For at vise i hvilken celle vi har fundet denne værdi, erklær en variabel mere som en streng.

Kode:

Sub RangeNext_Example () Dim Rng As Range Dim CellAdderess As String Set Rng = Range ("A2: A12") Rng.Find What: = "Bangalore" End Sub

Trin # 7 - Til denne variabel skal du tildele den fundne celleadresse.

Kode:

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 Sub
Bemærk: RNG. Adresse, fordi RNG vil have referencen for den fundne værdiecelle.

Trin # 8 - Vis nu resultatet af den tildelte celleadressevariabel i meddelelsesfeltet i 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 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

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

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 the cell A5. Click on the Ok button to see the next found value.

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

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

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

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

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

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

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

Trin # 21 - Dette vil fortsat vise alle de matchende celleadresser, og i sidste ende vil det vise meddelelsen som "Søgning er over" i det nye meddelelsesfelt.

Ting at huske

  • FIND-metoden kan kun finde en værdi ad gangen.
  • FIND NÆSTE i excel VBA kan finde den næste værdi fra den allerede fundet værdicelle.
  • Brug Do While-sløjfen til at løkke gennem alle cellerne i området.

Interessante artikler...