VBA POJDI NASLED - Kako uporabiti funkcijo FindNext v Excelu VBA?

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 je to funkcija 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 pravi beseda, »Najdi naprej« pomeni, da najdena celica nadaljuje 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 iskanja naslednje metode v excelu VBA.

Na primer, poglejte spodnje podatke.

1. korak - V teh podatkih moramo najti ime mesta "Bangalore." Začnimo s podproceduro v osnovnem vizualnem urejevalniku.

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 v tem območju, bomo iskali samo mesto "Bangalore".

Ker referenco obsega nastavimo 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
Opomba: RNG. Naslov, ker bo RNG imel referenco za celico najdene vrednosti.

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.

V celici A5 smo našli vrednost »Bangalore«. Z metodo Find lahko najdemo samo eno celico, zato moramo namesto FIND uporabiti FIND NEXT v excelu VBA.

10. korak - sklicevati se moramo na spremenljivko predmeta range, vendar z uporabo metode FIND NEXT v excelu VBA.

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 MsgBox CellAddress Set Rng = obseg ("A2: A12"). FindNext (Rng) End Sub

Kot lahko vidite zgoraj, smo uporabili metodo VBA FIND NEXT, znotraj funkcije pa ime spremenljivke predmeta obsega.

Korak # 11 - Znova dodelite naslov celice in v sporočilnem polju pokaži naslov.

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 MsgBox CellAddress Set Rng = obseg ("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 Ali MsgBox FindRng.Address Set FindRng = Rng.FindNext (FindRng) Loop While FirstCell FindRng.Address MsgBox "Iskanje je končano" End Sub

21. korak - Tako se bodo še naprej prikazovali vsi ujemajoči se naslovi celic, na koncu pa bo v novem polju za sporočilo prikazano sporočilo »Iskanje je končano«.

Stvari, ki si jih je treba zapomniti

  • Metoda FIND lahko hkrati najde samo eno vrednost.
  • FIND NEXT v excelu VBA lahko poišče naslednjo vrednost iz že najdene celice vrednosti.
  • Z zanko Do While zavrtite skozi vse celice v območju.

Zanimive Članki...