Excel VBA Najdi naprej
Tako kot v Excelu, ko pritisnemo CTRL + F, se prikaže čarovniško polje, ki nam omogoča iskanje vrednosti na določenem delovnem listu in ko je vrednost najdena, kliknite najdi poleg, da poiščemo drugo podobno vrednost, saj gre za funkcijo delovnega lista. lahko za iste namene uporablja tudi v VBA kot metodo lastnosti lastnosti kot application.findnext.
Iskanje določene vrednosti v omenjenem obsegu je v redu, toda kaj, če je zahteva najti vrednost z več pojavitvami. V enem od prejšnjih člankov smo razpravljali o metodi »Najdi« v VBA, ki sploh ni zapletena, vendar je iskanje vseh ponavljajočih se pojavov mogoče samo z metodo »Najdi naprej« v excelu VBA.
V tem članku vam bomo pokazali, kako uporabljati to »Najdi naprej« v Excelu VBA.
Kaj je Find Next v Excelu VBA?
Kot beseda pravi »Najdi naprej« pomeni, da iz najdene celice nadaljujemo z iskanjem naslednje vrednosti, dokler se ne vrne nazaj v prvotno celico, kjer smo začeli iskanje.
To je napredna različica metode "Find", ki v omenjenem obsegu išče samo enkrat omenjeno vrednost.
Spodaj je sintaksa metode FIND NEXT v Excelu VBA.
Po: To je beseda, ki jo iščemo.
Primeri metode Find Next v Excelu VBA
Spodaj so primeri Najdi naslednjo metodo v excelu VBA.
Na primer, poglejte spodnje podatke.
To predlogo VBA Find Next Excel lahko prenesete tukaj - VBA Find Next Excel Template
1. korak - V teh podatkih moramo najti ime mesta “Bangalore”. Začnimo s podproceduro v urejevalniku visual basic.
Koda:
Sub RangeNext_Example () End Sub
2. korak - Najprej spremenljivko razglasite kot objekt »Obseg«.
Koda:
Sub RangeNext_Example () Dim Rng As Range End Sub
3. korak - referenco za spremenljivko predmeta nastavite na »Obseg (« A2: A11 »).
Koda:
Sub RangeNext_Example () Dim Rng As Range Set Rng = Range ("A2: A12") End Sub 
Ker so naši podatki s seznama mest v območju celic od A2 do A11 samo v tem območju, bomo iskali mesto "Bangalore".
Ker nastavimo referenco obsega na spremenljivko "Rng", to spremenljivko uporabimo namesto, da vsakič uporabimo RANGE ("A2: A11").
4. korak - Uporabite spremenljivko RNG in odprite metodo Find.
Koda:
Sub RangeNext_Example () Dim Rng As Range Set Rng = Range ("A2: A12") Rng.Find End Sub 
5. korak - Prvi argument metode FIND je »Kaj«, torej tisto, kar poskušamo iskati v omenjenem obsegu, zato je vrednost, ki jo iščemo, »Bangalore«.
Koda:
Sub RangeNext_Example () Dim Rng As Range Set Rng = Range ("A2: A12") Rng.Find What: = "Bangalore" End Sub 
6. korak - Če želite prikazati, v kateri celici smo našli to vrednost, prijavite še eno spremenljivko kot niz.
Koda:
Sub RangeNext_Example () Dim Rng As Range Dim CellAddress As String Set Rng = Range ("A2: A12") Rng.Find What: = "Bangalore" End Sub 
7. korak - tej spremenljivki dodelite najdeni naslov celice.
Koda:
Sub RangeNext_Example () Dim Rng As Range Dim CellAddress As String Set Rng = Range ("A2: A12"). Find (What: = "Bangalore") Rng.Find What: = "Bangalore" CellAddress = Rng.Address End Sub 
Korak # 8 - Zdaj pripišite rezultat spremenljivke naslova celice v polju za sporočila v VBA.
Sub RangeNext_Example () Dim Rng As Range Dim CellAddress As String Set Rng = Range ("A2: A12"). Find (What: = "Bangalore") Rng.Find What: = "Bangalore" CellAddress = Rng.Address MsgBox CellAddress End Pod 
Korak # 9 - Zaženite kodo in poglejte, kaj dobimo tukaj.
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.