VBA PRONAĐI SLJEDEĆU - Kako koristiti funkciju FindNext u programu Excel VBA?

Excel VBA Pronađi dalje

Kao u Excelu kada pritisnemo CTRL + F, pojavit će se okvir čarobnjaka koji nam omogućuje pretragu vrijednosti u danom radnom listu i kada se vrijednost pronađe kliknemo na next (Pronađi) kako bismo pronašli drugu sličnu vrijednost, jer je to značajka radnog lista također ga može koristiti u VBA kao metodu svojstva aplikacije kao application.findnext za iste svrhe.

Pronalaženje određene vrijednosti u spomenutom rasponu je u redu, ali što ako je uvjet pronaći vrijednost s više pojavljivanja. U jednom od ranijih članaka raspravljali smo o metodi „Pronađi“ u VBA-u, koja uopće nije složena, ali pronalaženje svih ponavljajućih pojava moguće je samo pomoću metode „Pronađi sljedeće“ u excelu VBA.

U ovom ćemo vam članku pokazati kako koristiti ovo „Pronađi sljedeće“ u programu Excel VBA.

Što je Find Next u programu Excel VBA?

Kao što riječ kaže, "Pronađi sljedeće" znači iz pronađene ćelije nastaviti tražiti sljedeću vrijednost dok se ne vrati natrag u izvornu ćeliju u kojoj smo započeli pretragu.

Ovo je napredna verzija metode "Pronađi" koja pretražuje samo jednom spomenutu vrijednost u spomenutom rasponu.

Ispod je sintaksa metode FIND NEXT u programu Excel VBA.

Nakon: To je riječ koju tražimo.

Primjeri metode Pronađi sljedeću u programu Excel VBA

Ispod su primjeri pronalaska sljedeće metode u excelu VBA.

Na primjer, pogledajte podatke u nastavku.

1. korak - U tim podacima moramo pronaći naziv grada „Bangalore“. Pokrenimo potpostupak u osnovnom vizualnom uređivaču.

Kodirati:

Sub RangeNext_Example () Kraj Sub

Korak # 2 - Prvo proglasite varijablu objektom „Raspon“.

Kodirati:

Sub RangeNext_Example () Dim Rng As Range End Sub

Korak # 3 - Postavite referencu za varijablu objekta na "Raspon (" A2: A11 ").

Kodirati:

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

Budući da se naši podaci s popisa gradova nalaze u rasponu ćelija od A2 do A11 u tom rasponu, samo ćemo tražiti grad „Bangalore“.

Budući da smo referencu raspona postavili na varijablu "Rng", koristimo ovu varijablu umjesto da svaki put koristimo RANGE ("A2: A11").

Korak # 4 - Upotrijebite varijablu RNG i otvorite metodu Pronađi.

Kodirati:

Sub RangeNext_Example () Dim Rng As Range Set Rng = Range (Range ("A2: A12") Rng. Pronađi kraj Sub

Korak # 5 - Prvi argument metode FIND je "Što", tj. Ono što pokušavamo pretraživati ​​u spomenutom rasponu, pa je vrijednost koju pretražujemo "Bangalore".

Kodirati:

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

Korak # 6 - Da bismo pokazali u kojoj smo ćeliji pronašli ovu vrijednost, prijavite još jednu varijablu kao niz.

Kodirati:

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

Korak # 7 - Za ovu varijablu dodijelite pronađenu adresu ćelije.

Kodirati:

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
Napomena: RNG. Adresa jer će RNG imati referencu za ćeliju pronađene vrijednosti.

Korak # 8 - Sada u prikazu poruke u VBA prikaži rezultat dodijeljene varijable adrese ćelije.

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 Da li MsgBox FindRng.Address Set FindRng = Rng.FindNext (FindRng) Loop while FirstCell FindRng.Address MsgBox "Search is over" End Sub

Korak # 21 - Tako će se i dalje prikazivati ​​sve odgovarajuće adrese ćelija, a na kraju će se u novom okviru za poruke prikazati poruka "Pretraživanje je gotovo".

Stvari koje treba zapamtiti

  • Metoda FIND može istodobno pronaći samo jednu vrijednost.
  • FIND NEXT u excelu VBA može pronaći sljedeću vrijednost iz već pronađene ćelije vrijednosti.
  • Upotrijebite petlju Do While za petlju kroz sve ćelije u rasponu.

Zanimljivi članci...