Ovaj će vodič pokazati kako se koriste metode Find and Replace u programu Excel VBA.
VBA Find
Excel ima izvrsno ugrađeno Pronaći i Pronađi i zamijeni alata.
Mogu se aktivirati pomoću prečaca CTRL + F (Pronađi) ili CTRL + H (Zamijenite) ili putem vrpce: Početna> Uređivanje> Pronađi i odaberi.
Klikom Opcije, možete vidjeti napredne mogućnosti pretraživanja:
Pomoću VBA -a možete jednostavno pristupiti metodama Find and Replace. Ove su ugrađene metode daleko brže od svega što biste sami mogli napisati u VBA.
Pronađite primjer VBA
Kako bismo demonstrirali funkciju Find, stvorili smo sljedeći skup podataka u Sheet1.
Ako želite pratiti, unesite podatke u svoju radnu knjigu.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA Find bez opcijskih parametara
Kada koristite metodu VBA Find, postoji mnogo izbornih parametara koje možete postaviti.
Toplo preporučujemo definiranje svih parametara kad god koristite metodu pronalaženja!
Ako ne definirate opcionalne parametre, VBA će koristiti trenutno odabrane parametre u Excelovom prozoru Pronađi. To znači da možda ne znate koji se parametri pretraživanja koriste pri pokretanju koda. Nalaz se može pokrenuti u cijeloj radnoj knjizi ili na listu. Mogao bi tražiti formule ili vrijednosti. Nema načina da to znate, osim ako ručno ne provjerite što je trenutno odabrano u Excelovom prozoru za pronalaženje.
Radi jednostavnosti počet ćemo s primjerom bez definiranih izbornih parametara.
Primjer jednostavnog pronalaženja
Pogledajmo jednostavan primjer Find:
| 123456789 | Sub TestFind ()Dim MyRange As RangePostavi MyRange = Tablice ("List1"). UsedRange.Find ("zaposlenik")MsgBox MyRange.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowKraj podm |
Ovaj kôd traži "zaposlenika" u korištenom rasponu lista1. Ako pronađe "zaposlenika", dodijelit će prvi pronađeni raspon varijabli raspona MyRange.
Zatim će se prikazati okviri poruka s adresom, stupcem i retkom pronađenog teksta.
U ovom primjeru koriste se zadane postavke Find (pod pretpostavkom da nisu promijenjene u Excelovom prozoru Find):
- Tekst za pretraživanje djelomično se podudara s vrijednošću ćelije (nije potrebno točno podudaranje ćelije)
- Pretraživanje ne razlikuje velika i mala slova.
- Find traži samo jedan radni list
Ove se postavke mogu mijenjati različitim opcijskim parametrima (o kojima će biti riječi u nastavku).
Pronađite bilješke o metodama
- Find ne odabire ćeliju u kojoj se nalazi tekst. On samo identificira pronađeni raspon kojim možete manipulirati u svom kodu.
- Metoda Find locirat će samo prvu pronađenu instancu.
- Možete koristiti zamjenske znakove (*) npr. traži "E*"
ništa nije pronađeno
Ako tekst za pretraživanje ne postoji, objekt raspona će ostati prazan. To uzrokuje veliki problem kada vaš kôd pokuša prikazati vrijednosti lokacije jer one ne postoje. To će rezultirati porukom o pogrešci koju ne želite.
Srećom, možete isprobati prazan objekt raspona unutar VBA -e koristeći Is Operator:
| 1 | Ako nije, onda MyRange nije ništa |
Dodavanje koda u naš prethodni primjer:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangePostavi MyRange = Tablice ("List1"). UsedRange.Find ("zaposlenik")Ako nije, onda MyRange nije ništaMsgBox MyRange.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
Pronađi parametre
Do sada smo pogledali samo osnovni primjer korištenja metode Find. Međutim, dostupni su brojni izborni parametri koji će vam pomoći poboljšati pretraživanje
| Parametar | Tip | Opis | Vrijednosti |
| Što | Potreban | Vrijednost za traženje | Bilo koja vrsta podataka, poput niza ili numeričkih |
| Nakon | Izborno | Referenca jedne ćelije za početak pretraživanja | Adresa mobitela |
| Pogledaj u | Izborno | Za pretraživanje koristite formule, vrijednosti, komentare | xlVrijednosti, xlFormule, xlKomenti |
| Pogledaj | Izborno | Uskladite dio ili cijelu ćeliju | xlCijela, xl dio |
| SearchOrder | Izborno | Nalog za pretraživanje u - retke ili stupce | xlByRows, xlByColummns |
| SearchDirection | Izborno | Smjer za pretraživanje prema naprijed ili natrag | xlSljedeće, xlPrethodno |
| Kutija šibica | Izborno | Pretraživanje razlikuje mala ili velika slova | Istina ili laž |
| MatchByte | Izborno | Koristi se samo ako ste instalirali podršku za dvobajtne jezike, npr. kineski jezik | Istina ili laž |
| Format pretraživanja | Izborno | Dopusti pretraživanje prema formatu ćelije | Istina ili laž |
Nakon parametra i pronađite više vrijednosti
Vi koristite Nakon parametra za navođenje početne ćelije za vaše pretraživanje. Ovo je korisno ako postoji više od jedne instance vrijednosti koju tražite.
Ako je pretraživanje već pronašlo jednu vrijednost i znate da će biti pronađenih još vrijednosti, tada za pronalaženje prve instance koristite metodu Find s parametrom ‘After’, a zatim tu ćeliju koristite kao početnu točku za sljedeće pretraživanje.
Možete koristiti ovo za pronalaženje više instanci vašeg teksta za pretraživanje:
| 123456789101112131415161718192021222324252627282930313233343536 | PodispitMultipleFinds ()Dim MyRange kao raspon, OldRange kao raspon, FindStr kao niz'Potražite prvu instancu "" Svjetlo i toplina "Postavi MyRange = Listovi ("List 1"). UsedRange.Find ("Svjetlo i toplina")'Ako nije pronađen, izađiteAko MyRange nije ništa, izađite iz Sub'Prikaži prvu pronađenu adresuMsgBox MyRange.Adres'Napravite kopiju objekta rangePostavi OldRange = MyRange'Dodajte adresu nizu koji razdvaja s "|" likFindStr = FindStr & "|" & MyRange.Adresa'Ponovite opseg tražeći druge slučajeveČini'Potražite' Svjetlo i toplina 'koristeći prethodno pronađenu adresu kao parametar AfterPostavite MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Ako je adresa već pronađena, izađite iz do petlje - ovo zaustavlja kontinuirano petljeAko je InStr (FindStr, MyRange.Address), tada izađite iz Do'Prikaži najnoviju adresuMsgBox MyRange.Adres'Dodajte najnoviju adresu nizu adresaFindStr = FindStr & "|" & MyRange.Adresa'napraviti kopiju trenutnog rasponaPostavi OldRange = MyRangePetljaKraj podm |
Ovaj kôd će se ponavljati kroz korišteni raspon i prikazat će adresu svaki put kada pronađe instancu "Svjetlo i toplina"
Imajte na umu da će se kôd ponavljati sve dok se u FindStr -u ne pronađe duplicirana adresa, u tom slučaju će napustiti Do petlju.
LookIn parametar
Možete koristiti Parametar LookIn za navođenje komponente ćelije u kojoj želite pretraživati. Možete odrediti vrijednosti, formule ili komentare u ćeliji.
- xlVrijednosti - Pretražuje vrijednosti ćelija (konačna vrijednost ćelije nakon izračuna)
- xlFormule - Pretražuje unutar same formule ćelije (sve što je uneseno u ćeliju)
- xlKomenti - Pretražuje unutar bilješki ćelije
- xlCommentsThreaded - Pretrage unutar komentara ćelije
Pod pretpostavkom da je formula unesena na radni list, pomoću ovog primjera koda možete pronaći prvo mjesto bilo koje formule:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangePostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Ako ne, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
Ako je parametar 'LookIn' postavljen na xlValues, kod bi prikazao poruku 'Not Found'. U ovom primjeru vratit će B10.
Korištenje LookAt parametra
The Parametar LookAt određuje hoće li find tražiti točno podudaranje ćelije ili će tražiti bilo koju ćeliju koja sadrži vrijednost pretraživanja.
- xlCijela - Zahtijeva cijelu ćeliju da se podudara s vrijednošću pretraživanja
- xlPart - Unutar ćelije traži niz za pretraživanje
Ovaj primjer koda će locirati prvu ćeliju koja sadrži tekst "svjetlo". S Pogledaj: = xlDijeljak, vratit će utakmicu za "Light & Heat".
| 123456789 | Pod TestLookAt ()Dim MyRange As RangePostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Ako nije, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
Ako xlCijela je postavljeno, podudaranje bi se vratilo samo ako je vrijednost ćelije "svjetlo".
Parametar SearchOrder
The Parametar SearchOrder određuje kako će se pretraga provoditi u cijelom rasponu.
- xlRows - Pretraživanje se vrši red po red
- xlXolumni - Pretraživanje se vrši stupac po stupac
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangePostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("zaposlenik", SearchOrder: = xlColumns)Ako ne, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
To utječe na to koji će se par prvi pronaći.
Pomoću testnih podataka koji su ranije uneseni u radni list, kada je redoslijed pretraživanja stupci, locirana ćelija je A5. Kad se parametar naloga za pretraživanje promijeni u xlRows, ćelija koja se nalazi je C4
Ovo je važno ako imate dvostruke vrijednosti unutar raspona pretraživanja i želite pronaći prvu instancu pod određenim imenom stupca.
Parametar SearchDirection
The Parametar SearchDirection određuje u kojem će smjeru traženje ići - učinkovito naprijed ili natrag.
- xlSljedeće - Potražite sljedeću odgovarajuću vrijednost u rasponu
- xlPrethodno - Potražite prethodnu odgovarajuću vrijednost u rasponu
Opet, ako postoje dvostruke vrijednosti unutar raspona pretraživanja, to može utjecati na to da se ona prva pronađe.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangePostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrethodno)Ako nije, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
Koristeći ovaj kôd na testnim podacima, smjer pretraživanja xlPrevious vratit će lokaciju C9. Korištenje parametra xlNext vratit će lokaciju A4.
Parametar Next znači da će pretraživanje započeti u gornjem lijevom kutu raspona pretraživanja i raditi prema dolje. Prethodni parametar znači da će pretraživanje započeti u donjem desnom kutu raspona pretraživanja i raditi prema gore.
MatchByte parametar
The Parametar MatchBye koristi se samo za jezike koji koriste dvostruki bajt za predstavljanje svakog znaka, poput kineskog, ruskog i japanskog.
Ako je ovaj parametar postavljen na "True", Find će odgovarati samo dvobajtnim znakovima s dvobajtnim znakovima. Ako je parametar postavljen na 'False', tada će se dvobajtni znak podudarati s jednoznačnim ili dvobajtnim znakovima.
Parametar formata za pretraživanje
The Parametar SearchFormat omogućuje vam pretraživanje odgovarajućih formata ćelija. To može biti određeni font koji se koristi ili podebljani font ili boja teksta. Prije korištenja ovog parametra morate postaviti format potreban za pretraživanje pomoću svojstva Application.FindFormat.
Evo primjera kako ga koristiti:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = IstinaPostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Format pretraživanja: = True)Ako ne, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoApplication.FindFormat.ClearKraj podm |
U ovom primjeru, FindFormat svojstvo je postavljeno tako da traži podebljani font. Naredba Find zatim traži riječ 'heat' postavljajući parametar SearchFormat na True tako da će vratiti instancu tog teksta samo ako je font podebljan.
U prethodno prikazanim uzorcima podataka na radnom listu vratit će se A9, jedina ćelija koja sadrži riječ „toplina“ podebljanim fontom.
Provjerite je li svojstvo FindFormat obrisano na kraju koda. Ako ne napravite sljedeće pretraživanje, to će i dalje uzeti u obzir i vratiti netočne rezultate.
Tamo gdje koristite parametar SearchFormat, možete koristiti i zamjenski znak (*) kao vrijednost pretraživanja. U tom će slučaju tražiti bilo koju vrijednost podebljanim fontom:
| 1 | Postavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Format pretraživanja: = True) |
Korištenje više parametara
Svi ovdje opisani parametri pretraživanja mogu se koristiti međusobno u kombinaciji ako je potrebno.
Na primjer, mogli biste kombinirati parametar 'LookIn' s parametrom 'MatchCase' tako da pregledate cijeli tekst ćelije, ali razlikuju se između malih i velikih slova
| 123456789 | PodispitMultipleParameters ()Dim MyRange As RangePostavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Ako nije, onda MyRange nije ništaMsgBox MyRange.AdresDrugoMsgBox "Nije pronađeno"Završi akoKraj podm |
U ovom primjeru kôd će vratiti A4, ali ako smo koristili samo dio teksta, npr. "Topline", ništa se ne bi našlo jer se cijela vrijednost ćelije podudara. Također, ne bi uspio jer se slučaj ne podudara.
| 1 | Postavi MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Zamijenite u Excelu VBA
Kao što možete očekivati, postoji funkcija Zamijeni u Excelu VBA, koja radi na vrlo sličan način kao i "Pronađi", ali zamjenjuje vrijednosti na mjestu ćelije pronađene novom vrijednošću.
Ovo su parametri koje možete koristiti u naredbi Replace method. Oni djeluju na potpuno isti način kao i za iskaz metode Find. Jedina razlika u odnosu na "Pronađi" je ta što morate navesti zamjenski parametar.
| Ime | Tip | Opis | Vrijednosti |
| Što | Potreban | Vrijednost za traženje | Bilo koja vrsta podataka, poput niza ili numeričkih |
| Zamjena | Potreban | Zamjenski niz. | Bilo koja vrsta podataka, poput niza ili numeričkih |
| Pogledaj | Izborno | Uskladite dio ili cijelu ćeliju | xlPart ili xlCijela |
| SearchOrder | Izborno | Redoslijed pretraživanja u - Redovi ili Stupci | xlByRows ili xlByColumns |
| Kutija šibica | Izborno | Pretraživanje razlikuje mala ili velika slova | Istina ili laž |
| MatchByte | Izborno | Koristi se samo ako ste instalirali podršku za dvobajtne jezike | Istina ili laž |
| Format pretraživanja | Izborno | Dopusti pretraživanje prema formatu ćelije | Istina ili laž |
| Zamijeni format | Izborno | Zamjenski format metode. | Istina ili laž |
Parametar Zamijeni format traži ćeliju s određenim formatom, npr. podebljano na isti način na koji parametar SearchFormat radi u Find metodi. Morate najprije postaviti svojstvo Application.FindFormat, kao što je prikazano u ranije prikazanom kodu Find
Zamijenite bez dodatnih parametara
Najjednostavnije rečeno, trebate samo navesti što tražite i čime ga želite zamijeniti.
| 123 | Sub TestReplace ()Listovi ("List1"). UsedRange.Replace What: = "Svjetlo i toplina", Zamjena: = "L & H"Kraj podm |
Imajte na umu da će metoda Find vratiti samo prvu instancu podudarne vrijednosti, dok metoda Replace funkcionira kroz cijeli navedeni raspon i zamjenjuje sve na čemu pronađe podudaranje.
Ovdje prikazani zamjenski kôd zamijenit će svaku instancu "Svjetlo i toplina" s "L i H" kroz cijeli raspon ćelija definiranih objektom UsedRange
Korištenje VBA za pronalaženje ili zamjenu teksta unutar VBA tekstualnog niza
Gore navedeni primjeri odlično funkcioniraju kada koristite VBA za interakciju s Excel podacima. Međutim, za interakciju s VBA nizovima možete koristiti ugrađene VBA funkcije poput INSTR i REPLACE.
Možete koristiti INSTR funkcija za lociranje niza teksta unutar dužeg niza.
| 123 | Sub TestInstr ()MsgBox InStr ("Ovo je niz MyText", "MyText")Kraj podm |
Ovaj primjer koda vratit će vrijednost 9, što je brojčana pozicija u kojoj se "MyText" nalazi u nizu za pretraživanje.
Imajte na umu da razlikuje velika i mala slova. Ako je ‘Moj tekst’ mala slova, bit će vraćena vrijednost 0 što znači da niz za pretraživanje nije pronađen. U nastavku ćemo raspravljati o tome kako onemogućiti osjetljivost na velika i mala slova.
INSTR - Počni
Dostupna su još dva izborna parametra. Možete odrediti početnu točku za pretraživanje:
| 1 | MsgBox InStr (9, "Ovo je niz MyText", "MyText") |
Početna točka je navedena kao 9 pa će se i dalje vraćati 9. Ako je početna točka bila 10, tada bi se vratilo 0 (bez podudaranja) jer bi početna točka bila previše naprijed.
INSTR - Osjetljivost velikih i malih slova
Također možete postaviti parametar Usporedi na vbBinaryCompare ili vbTextCompare. Ako postavite ovaj parametar, izraz mora imati vrijednost početnog parametra.
- vbBinaryCompare - velika i mala slova (zadano)
- vbTextCompare - Ne razlikuje velika i mala slova
| 1 | MsgBox InStr (1, "Ovo je niz MyText", "mytext", vbTextCompare) |
Ova izjava će i dalje vraćati 9, iako je tekst za pretraživanje malim slovima.
Da biste onemogućili razlikovanje malih i velikih slova, možete proglasiti i opciju Usporedi tekst na vrhu modula koda.
VBA funkcija zamjene
Ako želite zamijeniti znakove u nizu s različitim tekstom u vašem kodu, tada je metoda Zamijena idealna za ovo:
| 123 | Sub TestReplace ()MsgBox Replace ("Ovo je niz MyText", "MyText", "My Text")Kraj podm |
Ovaj kôd zamjenjuje "MyText" s "My Text". Imajte na umu da niz za pretraživanje razlikuje velika i mala slova jer je binarna usporedba zadana.
Možete dodati i druge izborne parametre:
- Početak - definira položaj u početnom nizu od kojeg zamjena mora početi. Za razliku od metode Find, ona vraća skraćeni niz koji počinje od broja znakova definiranog parametrom Start.
- Računati - definira broj zamjena koje treba izvršiti. Prema zadanim postavkama, Replace će promijeniti svaku instancu pronađenog teksta za pretraživanje, ali to možete ograničiti na jednu zamjenu postavljanjem parametra Count na 1
- Usporedi - kao u Find metodi možete odrediti binarno pretraživanje ili tekstualno pretraživanje pomoću vbBinaryCompare ili vbTextCompare. Binarno razlikuje velika i mala slova, a tekst ne razlikuje velika i mala slova
| 1 | MsgBox Replace ("Ovo je niz MyText (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Ovaj kôd vraća "Moj tekstni niz (moj tekst)". To je zato što je zadana početna točka 9, pa novi vraćeni niz počinje od znaka 9. Promijenjen je samo prvi ‘MyText’ jer je parametar Count postavljen na 1.
Metoda zamjene idealna je za rješavanje problema poput imena ljudi koji sadrže apostrofe, npr. O’Flynn. Ako koristite pojedinačne navodnike za definiranje vrijednosti niza, a postoji apostrof, to će uzrokovati pogrešku jer će kôd interpretirati apostrof kao kraj niza i neće prepoznati ostatak niza.
Možete upotrijebiti metodu Zamijeni kako biste apostrof zamijenili ničim, potpuno ga uklonivši.
