Ta vadnica bo pokazala, kako uporabljati metode Najdi in zamenjaj v Excelu VBA.
VBA Find
Excel ima odlično vgrajeno Najti in Poišči in zamenjaj orodja.
Lahko jih aktivirate s bližnjicami CTRL + F (Poišči) oz CTRL + H (Zamenjaj) ali skozi trak: Domov> Urejanje> Poišči in izberi.
S klikom Opcije, si lahko ogledate napredne možnosti iskanja:
Z metodami VBA lahko preprosto dostopate do metod Najdi in Zamenjaj. Te vgrajene metode so veliko hitrejše od vsega, kar bi lahko sami zapisali v VBA.
Poiščite primer VBA
Za prikaz funkcionalnosti iskanja smo v Sheet1 ustvarili naslednji niz podatkov.
Če želite nadaljevati, vnesite podatke v svoj delovni zvezek.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find brez izbirnih parametrov
Pri uporabi metode VBA Find lahko nastavite veliko neobveznih parametrov.
Močno priporočamo, da določite vse parametre, kadar koli uporabite metodo Najdi!
Če ne določite izbirnih parametrov, bo VBA uporabil trenutno izbrane parametre v Excelovem oknu Najdi. To pomeni, da morda ne veste, kateri iskalni parametri se uporabljajo pri izvajanju kode. Iskanje je mogoče prikazati na celotnem delovnem zvezku ali na listu. Lahko išče formule ali vrednosti. Ni mogoče vedeti, razen če ročno preverite, kaj je trenutno izbrano v Excelovem oknu za iskanje.
Zaradi poenostavitve bomo začeli s primerom brez opredeljenih izbirnih parametrov.
Primer preprostega iskanja
Poglejmo preprost primer iskanja:
| 123456789 | Sub TestFind ()Zatemni MyRange As RangeNastavi MyRange = Listi ("List1"). UsedRange.Find ("zaposleni")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Ta koda išče "zaposlenega" v uporabljenem razponu lista1. Če najde »zaposlenega«, bo prvo najdeno območje dodelil spremenljivki obsega MyRange.
Nato se prikažejo polja s sporočili z naslovom, stolpcem in vrstico najdenega besedila.
V tem primeru so uporabljene privzete nastavitve iskanja (ob predpostavki, da niso bile spremenjene v Excelovem oknu za iskanje):
- Iskalno besedilo se delno ujema z vrednostjo celice (natančno ujemanje celice ni potrebno)
- Iskanje ne razlikuje med velikimi in malimi črkami.
- Poišči poišče samo en sam delovni list
Te nastavitve lahko spremenite z različnimi izbirnimi parametri (obravnavano spodaj).
Poiščite opombe o metodah
- Najdi ne izbere celice, v kateri je besedilo. Opredeljuje le najdeno območje, s katerim lahko upravljate v svoji kodi.
- Metoda Find bo poiskala samo prvi najdeni primerek.
- Uporabite lahko nadomestne znake (*) npr. poišči "E*"
Nič ni bilo najdenega
Če iskalno besedilo ne obstaja, bo objekt obsega ostal prazen. To povzroča velike težave, ko koda poskuša prikazati vrednosti lokacije, ker ne obstajajo. To bo povzročilo sporočilo o napaki, ki ga ne želite.
Na srečo lahko z operaterjem Is Operator preizkusite objekt praznega obsega v VBA:
| 1 | Če ni, potem MyRange ni nič |
Dodajanje kode v naš prejšnji primer:
| 12345678910111213 | Sub TestFind ()Zatemni MyRange As RangeNastavi MyRange = Listi ("List1"). UsedRange.Find ("zaposleni")Če ni, potem MyRange ni ničMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
Poiščite parametre
Doslej smo pogledali le osnovni primer uporabe metode Find. Vendar pa so na voljo številni izbirni parametri, ki vam pomagajo izboljšati iskanje
| Parameter | Vrsta | Opis | Vrednote |
| Kaj | Obvezno | Vrednost za iskanje | Vse vrste podatkov, na primer niz ali številka |
| Po | Neobvezno | Sklic na eno celico za začetek iskanja | Naslov celice |
| Poglej v | Neobvezno | Za iskanje uporabite formule, vrednosti, komentarje | xlVrednosti, xlFormule, xlKomenti |
| Poglej | Neobvezno | Ujemajte del ali celoto celice | xlVse, xlPart |
| SearchOrder | Neobvezno | Naročilo za iskanje v - vrsticah ali stolpcih | xlByRows, xlByColummns |
| SearchDirection | Neobvezno | Smer iskanja - naprej ali nazaj | xlNaprej, xlPrejšnji |
| MatchCase | Neobvezno | Iskanje je odvisno od velikih ali malih črk | Pravilno ali napačno |
| MatchByte | Neobvezno | Uporablja se le, če ste namestili podporo za dvobajtne jezike, npr. kitajski jezik | Pravilno ali napačno |
| Format iskanja | Neobvezno | Dovoli iskanje po obliki celice | Pravilno ali napačno |
Po parametru in poiščite več vrednosti
Uporabljate Po parametru da določite začetno celico za iskanje. To je uporabno, če obstaja več primerov vrednosti, ki jo iščete.
Če je iskanje že našlo eno vrednost in veste, da bo najdenih več vrednosti, potem uporabite metodo Najdi s parametrom »Po« za snemanje prvega primerka in nato to celico uporabite kot izhodišče za naslednje iskanje.
To lahko uporabite za iskanje več primerkov iskalnega besedila:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Zatemni MyRange kot obseg, OldRange kot obseg, FindStr kot niz'Poiščite prvi primerek "' Light & Heat"Set MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")'Če ga ne najdete, zapustiteČe MyRange ni nič, zapustite Sub'Prikaz prvega najdenega naslovaMsgBox MyRange.Address'Naredite kopijo predmeta obsegaNastavi OldRange = MyRange'Dodajte naslov nizu z "|" karakterFindStr = FindStr & "|" & MyRange.Address'Ponovite po obsegu in poiščite druge primereNaredi'Poiščite' Light & Heat 'z uporabo prejšnjega najdenega naslova kot parameter AfterSet MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Če je naslov že najden, potem zapustite do zanko - to ustavi neprekinjeno zankoČe je InStr (FindStr, MyRange.Address), potem zapustite Do'Prikaz zadnjega najdenega naslovaMsgBox MyRange.Address'V niz naslovov dodajte najnovejši naslovFindStr = FindStr & "|" & MyRange.Address'naredite kopijo trenutnega obsegaNastavi OldRange = MyRangeZankaEnd Sub |
Ta koda se bo ponavljala skozi uporabljeni obseg in prikazala bo naslov vsakič, ko najde primerek »Svetloba in toplota«
Upoštevajte, da se bo koda obnavljala, dokler v FindStrju ne najde podvojenega naslova, v tem primeru bo zapustila zanko Do.
Poglej v parametru
Uporabite lahko Parameter LookIn , da določite, v kateri komponenti celice želite iskati. V celici lahko določite vrednosti, formule ali komentarje.
- xlVrednosti - Išče vrednosti celic (končna vrednost celice po njenem izračunu)
- xlFormule - Iskanja znotraj same formule celice (karkoli je vneseno v celico)
- xlKomentiraj - Iskanja v zapiskih celice
- xlCommentsThreaded - Iskanja v komentarjih celic
Ob predpostavki, da je formula vnesena na delovnem listu, lahko s to primerno kodo poiščete prvo mesto katere koli formule:
| 12345678910 | Sub TestLookIn ()Zatemni MyRange As RangeNastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
Če je bil parameter 'LookIn' nastavljen na xlValues, bi koda prikazala sporočilo 'Not Found'. V tem primeru bo vrnil B10.
Uporaba parametra LookAt
The Parameter LookAt določa, ali bo funkcija find iskala natančno ujemanje celic ali poiskala katero koli celico, ki vsebuje vrednost iskanja.
- xlVse - Zahteva, da se celotna celica ujema z vrednostjo iskanja
- xlPart - V celici išče iskalni niz
Ta primer kode bo poiskal prvo celico z besedilom "light". Z Poglej: = xlDel, vrnil bo ujemanje za “Light & Heat”.
| 123456789 | Sub TestLookAt ()Zatemni MyRange As RangeNastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
Če xlVse je bila nastavljena, se ujemanje vrne le, če je vrednost celice "lahka".
Parameter SearchOrder
The Parameter SearchOrder narekuje, kako bo iskanje potekalo v celotnem obsegu.
- xlRows - Iskanje poteka po vrsticah
- xlXolumns - Iskanje poteka po stolpcih
| 123456789 | Sub TestSearchOrder ()Zatemni MyRange As RangeNastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("zaposleni", SearchOrder: = xlColumns)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
To vpliva na to, katera tekma bo prva najdena.
Z uporabo preskusnih podatkov, vnesenih na delovni list prej, ko je vrstni red iskanja stolpci, je celica A5. Ko se parameter vrstnega reda iskanja spremeni v xlRows, je celica, ki se nahaja, C4
To je pomembno, če imate v obsegu iskanja podvojene vrednosti in želite prvi primerek poiskati pod določenim imenom stolpca.
Parameter SearchDirection
The Parameter SearchDirection narekuje, v katero smer bo iskanje potekalo - učinkovito naprej ali nazaj.
- xlNaprej - Poiščite naslednjo ujemajočo se vrednost v razponu
- xlPrejšnji - Poiščite prejšnjo ujemajočo se vrednost v razponu
Še enkrat, če so v obsegu iskanja podvojene vrednosti, lahko to vpliva na to, da je ena najdena prva.
| 12345678910 | Sub TestSearchDirection ()Zatemni MyRange As RangeNastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
S to kodo na testnih podatkih bo iskalna smer xlPrevious vrnila lokacijo C9. Uporaba parametra xlNext bo vrnila lokacijo A4.
Parameter Next pomeni, da se bo iskanje začelo v zgornjem levem kotu obsega iskanja in delovalo navzdol. Prejšnji parameter pomeni, da se bo iskanje začelo v spodnjem desnem kotu obsega iskanja in delovalo navzgor.
Parameter MatchByte
The Parameter MatchBye se uporablja samo za jezike, ki uporabljajo dvojni bajt za predstavitev vsakega znaka, kot so kitajščina, ruščina in japonščina.
Če je ta parameter nastavljen na "True", se bo Find najdal samo z dvobajtnimi znaki z dvobajtnimi znaki. Če je parameter nastavljen na 'False', se bo dvobajtni znak ujemal z eno- ali dvobajtnimi znaki.
Parameter iskalnega formata
The Parameter SearchFormat omogoča iskanje ustreznih oblik celic. To je lahko določena pisava, ki se uporablja, krepka pisava ali barva besedila. Pred uporabo tega parametra morate z lastnostjo Application.FindFormat nastaviti obliko, ki je potrebna za iskanje.
Tukaj je primer, kako ga uporabljati:
| 12345678910111213 | Sub TestSearchFormat ()Zatemni MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = ResNastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeApplication.FindFormat.ClearEnd Sub |
V tem primeru je FindFormat lastnost je nastavljena tako, da išče krepko pisavo. Stavek Find nato poišče besedo "toplota" in nastavi parameter SearchFormat na True, tako da bo vrnil primerek tega besedila le, če je pisava krepka.
V prej prikazanih vzorčnih podatkih na delovnem listu bo vrnjeno A9, ki je edina celica, ki v krepki pisavi vsebuje besedo „toplota“.
Prepričajte se, da je lastnost FindFormat počiščena na koncu kode. Če tega ne storite pri naslednjem iskanju, boste to še vedno upoštevali in vrnili napačne rezultate.
Če uporabljate parameter SearchFormat, lahko kot vrednost iskanja uporabite tudi nadomestni znak (*). V tem primeru bo poiskal katero koli vrednost s krepko pisavo:
| 1 | Nastavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Uporaba več parametrov
Vse tukaj opisane iskalne parametre lahko po potrebi uporabite v kombinaciji med seboj.
Parameter »LookIn« lahko na primer združite s parametrom »MatchCase«, tako da pogledate celotno besedilo celice, vendar je odvisno od malih in velikih črk
| 123456789 | Pod Test Več parametrov ()Zatemni MyRange As RangeSet MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Če ni, potem MyRange ni ničMsgBox MyRange.AddressSicer paMsgBox "Ni najdeno"Konec ČeEnd Sub |
V tem primeru bo koda vrnila A4, če pa smo uporabili le del besedila npr. "Toplote", ne bi našli ničesar, ker se po celotni vrednosti celice ujemamo. Prav tako ne bi uspelo, ker se primer ne ujema.
| 1 | Set MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Zamenjaj v Excelu VBA
Kot lahko pričakujete, obstaja funkcija Zamenjava v Excelu VBA, ki deluje na zelo podoben način kot »Najdi«, vendar nadomesti vrednosti na lokaciji celice z novo vrednostjo.
To so parametri, ki jih lahko uporabite v stavku Replace method. Ti delujejo popolnoma enako kot pri stavku metode Find. Edina razlika pri "Najdi" je, da morate podati parameter Zamenjava.
| Ime | Vrsta | Opis | Vrednote |
| Kaj | Obvezno | Vrednost za iskanje | Vse vrste podatkov, na primer niz ali številka |
| Zamenjava | Obvezno | Nadomestni niz. | Vse vrste podatkov, na primer niz ali številka |
| Poglej | Neobvezno | Ujemajte del ali celoto celice | xlPart ali xlWhole |
| Išči naročilo | Neobvezno | Vrstni red iskanja - vrstice ali stolpci | xlByRows ali xlByColumns |
| MatchCase | Neobvezno | Iskanje je odvisno od velikih ali malih črk | Pravilno ali napačno |
| MatchByte | Neobvezno | Uporablja se samo, če ste namestili podporo za dvobajtne jezike | Pravilno ali napačno |
| Format iskanja | Neobvezno | Dovoli iskanje po obliki celice | Pravilno ali napačno |
| Zamenjaj format | Neobvezno | Oblika zamenjave metode. | Pravilno ali napačno |
Parameter Replace Format išče celico z določeno obliko, npr. krepko na enak način, ko parameter FindFormat deluje v metodi Find. Najprej morate nastaviti lastnost Application.FindFormat, kot je prikazano v prej prikazani kodi Najdi
Zamenjajte brez izbirnih parametrov
Najenostavneje morate samo določiti, kaj iščete in s čim ga želite zamenjati.
| 123 | Sub TestReplace ()Listi ("List1"). UsedRange.Replace What: = "Svetloba in toplota", Zamenjava: = "L & H"End Sub |
Upoštevajte, da bo metoda Find vrnila samo prvi primerek ujemajoče se vrednosti, medtem ko metoda Replace deluje skozi celotno podano območje in nadomešča vse, za kar najde ujemanje.
Tu prikazana nadomestna koda bo nadomestila vsak primerek »Svetloba in toplota« z »L & H« v celotnem obsegu celic, ki jih definira objekt UsedRange
Uporaba VBA za iskanje ali zamenjavo besedila v nizu besedila VBA
Zgornji primeri odlično delujejo pri uporabi VBA za interakcijo z Excelovimi podatki. Za interakcijo z nizi VBA lahko uporabite vgrajene funkcije VBA, kot sta INSTR in REPLACE.
Uporabite lahko Funkcija INSTR za iskanje niza besedila v daljšem nizu.
| 123 | Sub TestInstr ()MsgBox InStr ("To je niz MyText", "MyText")End Sub |
Ta primer kode bo vrnil vrednost 9, ki je položaj številke, kjer je v nizu, ki ga je treba iskati, "MyText".
Upoštevajte, da razlikuje velike in male črke. Če je »MyText« vse male črke, se vrne vrednost 0, kar pomeni, da iskalni niz ni bil najden. Spodaj bomo razpravljali o tem, kako onemogočiti občutljivost na velike in male črke.
INSTR - Začni
Na voljo sta še dva dodatna parametra. Določite lahko začetno točko iskanja:
| 1 | MsgBox InStr (9, "To je niz MyText", "MyText") |
Začetna točka je določena kot 9, zato se bo še vedno vrnila 9. Če bi bila začetna točka 10, bi vrnila 0 (brez ujemanja), saj bi bila izhodiščna točka predaleč naprej.
INSTR - Občutljivost velikih in malih črk
Parameter Primerjaj lahko nastavite tudi na vbBinaryCompare ali vbTextCompare. Če nastavite ta parameter, mora imeti stavek vrednost začetnega parametra.
- vbBinaryCompare - velike in male črke (privzeto)
- vbTextCompare - Ne razlikuje med velikimi in malimi črkami
| 1 | MsgBox InStr (1, "To je niz MyText", "mytext", vbTextCompare) |
Ta stavek bo še vedno vrnil 9, čeprav je iskalno besedilo z malimi črkami.
Če želite onemogočiti občutljivost velikih in malih črk, lahko razglasite tudi možnost Primerjaj besedilo na vrhu kode.
Funkcija zamenjave VBA
Če želite v kodi zamenjati znake v nizu z drugačnim besedilom, je metoda Replace idealna za to:
| 123 | Sub TestReplace ()MsgBox Replace ("To je niz MyText", "MyText", "My Text")End Sub |
Ta koda nadomesti »MyText« z »My Text«. Upoštevajte, da iskalni niz razlikuje velike in male črke, saj je privzeta binarna primerjava.
Dodate lahko tudi druge neobvezne parametre:
- Začni - določa položaj v začetnem nizu, od katerega mora zamenjava začeti. Za razliko od metode Find vrne okrnjeni niz, ki se začne od številke znaka, ki jo definira parameter Start.
- Preštej - določa število zamenjav, ki jih je treba opraviti. Replace privzeto spremeni vsak primerek najdenega iskalnega besedila, vendar lahko to omejite na eno samo zamenjavo tako, da nastavite parameter Count na 1
- Primerjaj - tako kot pri metodi iskanja lahko z uporabo določite binarno iskanje ali besedilno iskanje vbBinaryCompare ali vbTextCompare. Binarni podatki razlikujejo med velikimi in malimi črkami, besedilo pa ne razlikuje med velikimi in malimi črkami
| 1 | MsgBox Replace ("To je niz MyText (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Ta koda vrne "My Text string (mytext)". To je zato, ker je začetna točka 9, zato se novi vrnjeni niz začne pri znaku 9. Spremenjen je bil samo prvi "MyText", ker je parameter Count nastavljen na 1.
Metoda Replace je idealna za reševanje problemov, kot so imena ljudi, ki vsebujejo apostrofe, npr. O'Flynn. Če uporabljate enojne narekovaje za določitev vrednosti niza in obstaja apostrof, bo to povzročilo napako, ker bo koda razlagala apostrof kot konec niza in ne bo prepoznala preostalega niza.
Lahko uporabite metodo Replace, da apostrof zamenjate z ničemer in ga popolnoma odstranite.
