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 kan også bruge det i VBA som Application-egenskabsmetode som application.findnext til de 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 "Find næste" betyder, at fra den fundne celle skal du fortsætte med at søge efter den næste værdi, indtil du vender tilbage til den oprindelige celle, hvor vi har startet søgningen.
Dette er den avancerede version af "Find" -metoden, der 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å find næste metode i excel VBA.
Se for eksempel nedenstående data.
Du kan downloade denne VBA Find næste Excel-skabelon her - VBA Find næste Excel-skabelon
Trin # 1 - I disse data skal vi finde bynavnet “Bangalore”. Lad os starte delproceduren i den visuelle grundeditor.
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 over bylisten er der i området fra celler fra A2 til A11 i dette område, skal vi kun 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 forsøger 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 tildeles den fundne celle-adresse.
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 
Trin # 8 - Vis nu resultatet af den tildelte celleadressevariabel i meddelelsesfeltet i VBA.
Sub RangeNext_Example () Dim Rng Som Range Dim CellAdderess As String Set Rng = Range ("A2: A12"). Find (What: = "Bangalore") Rng.Find What: = "Bangalore" CellAddress = Rng.Address MsgBox CellAddress End Sub 
Trin # 9 - Kør koden og se hvad vi får her.
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 Step#21 – This will keep showing all the matching cell address and in the end, it will show the message as “Search is Over” in the new message box.
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 Do While loop to loop through all the cells in the range.