Acest tutorial va arăta cum să utilizați metodele Găsiți și înlocuiți în Excel VBA.
Găsire VBA
Excel are încorporat excelent Găsi și Găsiți și înlocuiți instrumente.
Acestea pot fi activate cu comenzile rapide CTRL + F (Găsiți) sau CTRL + H (Înlocuiți) sau prin panglică: Acasă> Editare> Găsire și selectare.
Dând clicuri Opțiuni, puteți vedea opțiuni de căutare avansată:
Puteți accesa cu ușurință atât metodele Găsiți, cât și Înlocuiți folosind VBA. Aceste metode încorporate sunt mult mai rapide decât orice puteți scrie în VBA.
Găsiți un exemplu VBA
Pentru a demonstra funcționalitatea Găsiți, am creat următorul set de date în Sheet1.
Dacă doriți să continuați, introduceți datele în propriul registru de lucru.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Găsire VBA fără parametri opționali
Când utilizați metoda VBA Find, există mulți parametri opționali pe care îi puteți seta.
Vă recomandăm cu tărie să definiți toți parametrii ori de câte ori utilizați metoda Găsiți!
Dacă nu definiți parametrii opționali, VBA va utiliza parametrii selectați în prezent în fereastra Găsire Excel. Aceasta înseamnă că este posibil să nu știți ce parametri de căutare sunt utilizați atunci când codul este rulat. Găsirea poate fi difuzată pe întregul registru de lucru sau pe o foaie. Ar putea căuta formule sau valori. Nu există nicio modalitate de a ști, cu excepția cazului în care verificați manual ce este selectat în prezent în fereastra Căutare Excel.
Pentru simplitate, vom începe cu un exemplu fără parametri opționali definiți.
Exemplu de căutare simplă
Să vedem un exemplu simplu de Găsire:
| 123456789 | Sub TestFind ()Dim MyRange As RangeSetați MyRange = Foi ("Sheet1"). UsedRange.Find ("angajat")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowSfârșitul Sub |
Acest cod caută „angajat” în gama utilizată a foii1. Dacă găsește „angajat”, va atribui primul interval găsit variabilei MyRange.
Apoi, se vor afișa casetele de mesaje cu adresa, coloana și rândul textului găsit.
În acest exemplu, sunt utilizate setările de căutare implicite (presupunând că nu au fost modificate în fereastra de căutare a Excel):
- Textul căutării este parțial asociat cu valoarea celulei (nu este necesară o potrivire exactă a celulei)
- Căutarea nu ține cont de majuscule și minuscule.
- Găsiți caută numai o singură foaie de lucru
Aceste setări pot fi modificate cu diferiți parametri opționali (discutați mai jos).
Găsiți note despre metodă
- Căutare nu selectează celula în care este găsit textul. Identifică doar intervalul găsit, pe care îl puteți manipula în codul dvs.
- Metoda Find va localiza doar prima instanță găsită.
- Puteți utiliza metacaractere (*) de ex. căutați „E *”
nimic gasit
Dacă textul de căutare nu există, atunci obiectul interval va rămâne gol. Acest lucru cauzează o problemă majoră atunci când codul dvs. încearcă să afișeze valorile locației, deoarece acestea nu există. Acest lucru va duce la un mesaj de eroare pe care nu îl doriți.
Din fericire, puteți testa un obiect cu interval liber în VBA folosind Operatorul este:
| 1 | Dacă nu MyRange nu este nimic atunci |
Adăugarea codului la exemplul nostru anterior:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeSetați MyRange = Foi ("Sheet1"). UsedRange.Find ("angajat")Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowAltfelMsgBox "Not found"End IfSfârșitul Sub |
Găsiți parametrii
Până în prezent, am analizat doar un exemplu de bază al utilizării metodei Find. Cu toate acestea, există o serie de parametri opționali disponibili pentru a vă ajuta să vă rafinați căutarea
| Parametru | Tip | Descriere | Valori |
| Ce | Necesar | Valoarea de căutat | Orice tip de date, cum ar fi un șir sau numeric |
| După | Opțional | Referință pentru o singură celulă pentru a începe căutarea | Adresa celulei |
| Uită-te în | Opțional | Folosiți Formule, Valori, Comentarii pentru căutare | xlValues, xlFormulas, xlComments |
| Uita-te la | Opțional | Potriviți o parte sau întreaga dintr-o celulă | xlWhole, xlPart |
| SearchOrder | Opțional | Ordinea de căutare în - rânduri sau coloane | xlByRows, xlByColummns |
| Căutare direcție | Opțional | Direcția de căutare pentru a merge în - înainte sau înapoi | xlNext, xlPrevious |
| MatchCase | Opțional | Căutarea este diferențiată de majuscule sau minuscule | Adevărat sau fals |
| MatchByte | Opțional | Folosit numai dacă ați instalat suport de limbă dublu octet de ex. limba chineza | Adevărat sau fals |
| SearchFormat | Opțional | Permite căutarea după formatul celulei | Adevărat sau fals |
După parametru și Găsiți valori multiple
Folosești După parametru pentru a specifica celula de pornire pentru căutarea dvs. Acest lucru este util atunci când există mai multe instanțe din valoarea pe care o căutați.
Dacă o căutare a găsit deja o valoare și știți că vor fi găsite mai multe valori, atunci folosiți metoda Găsiți cu parametrul „După” pentru a înregistra prima instanță și apoi utilizați acea celulă ca punct de plecare pentru următoarea căutare.
Puteți utiliza acest lucru pentru a găsi mai multe instanțe ale textului dvs. de căutare:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String„Căutați prima instanță a„ ‘Light & Heat”Setați MyRange = Foi ("Sheet1"). UsedRange.Find ("Light & Heat")Dacă nu este găsit, ieșițiDacă MyRange nu este nimic, ieșiți din Sub'Afișați prima adresă găsităMsgBox MyRange.Address'Faceți o copie a obiectului de gamăSet OldRange = MyRange'Adăugați adresa la șirul care delimitează cu un "|" caracterFindStr = FindStr & "|" & MyRange.Address'Iterează prin gamă căutând alte instanțeDo„Căutați„ Light & Heat ”utilizând adresa găsită anterior ca parametru AfterSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))„Dacă adresa a fost deja găsită, atunci ieșiți din bucla do - aceasta oprește buclarea continuăDacă InStr (FindStr, MyRange.Address), apoi ieșiți din Do'Afișați ultima adresă găsităMsgBox MyRange.Address'Adăugați cea mai recentă adresă la șirul de adreseFindStr = FindStr & "|" & MyRange.Address'faceți o copie a intervalului curentSet OldRange = MyRangeBuclăSfârșitul Sub |
Acest cod va itera prin intervalul utilizat și va afișa adresa de fiecare dată când găsește o instanță de „Light & Heat”
Rețineți că codul va continua să se bucle până când se găsește o adresă duplicat în FindStr, caz în care va ieși din bucla Do.
LookIn Parameter
Puteți utiliza Parametru LookIn pentru a specifica în ce componentă a celulei doriți să căutați. Puteți specifica valori, formule sau comentarii într-o celulă.
- xlValues - Caută valorile celulei (valoarea finală a unei celule după calcul)
- xlFormule - Căutări în formula celulei în sine (orice este introdus în celulă)
- xlComentarii - Căutări în note de celulă
- xlCommentsThreaded - Căutări în comentariile celulei
Presupunând că a fost introdusă o formulă pe foaia de lucru, puteți utiliza acest exemplu de cod pentru a găsi prima locație a oricărei formule:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfSfârșitul Sub |
Dacă parametrul „LookIn” a fost setat la xlValues, codul va afișa un mesaj „Not Found”. În acest exemplu va returna B10.
Utilizarea parametrului LookAt
The Parametru LookAt determină dacă găsiți va căuta o potrivire exactă a celulei sau căuta orice celulă care conține valoarea de căutare.
- xlWhole - Necesită ca întreaga celulă să se potrivească cu valoarea de căutare
- xlPart - Caută într-o celulă șirul de căutare
Acest exemplu de cod va localiza prima celulă care conține textul „lumină”. Cu Lookat: = xlPart, va returna un meci pentru „Light & Heat”.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeSetați MyRange = Foi ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfSfârșitul Sub |
Dacă xlWhole a fost setată, o potrivire se va întoarce numai dacă valoarea celulei este „ușoară”.
Parametru SearchOrder
The Parametru SearchOrder dictează modul în care va fi efectuată căutarea în întreaga gamă.
- xlRows - Căutarea se face rând cu rând
- xlXolumns - Căutarea se face coloană cu coloană
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("angajat", SearchOrder: = xlColumns)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfSfârșitul Sub |
Acest lucru influențează care se potrivește mai întâi.
Folosind datele de test introduse anterior în foaia de lucru, când ordinea de căutare este coloană, celula localizată este A5. Când parametrul comenzii de căutare este schimbat în xlRows, celula localizată este C4
Acest lucru este important dacă aveți valori duplicate în intervalul de căutare și doriți să găsiți prima instanță sub un anumit nume de coloană.
Parametru SearchDirection
The Parametru SearchDirection dictează în ce direcție va merge căutarea - efectiv înainte sau înapoi.
- xlNext - Căutați următoarea valoare de potrivire din interval
- xlPrevious - Căutați valoarea de potrivire anterioară în interval
Din nou, dacă există valori duplicat în intervalul de căutare, acesta poate avea un efect asupra căruia se găsește mai întâi.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfSfârșitul Sub |
Folosind acest cod pe datele de testare, o direcție de căutare xlPrevious va returna o locație C9. Utilizarea parametrului xlNext va returna o locație A4.
Parametrul Următor înseamnă că căutarea va începe în colțul din stânga sus al intervalului de căutare și va funcționa în jos. Parametrul anterior înseamnă că căutarea va începe în colțul din dreapta jos al intervalului de căutare și va funcționa în sus.
Parametru MatchByte
The Parametru MatchBye este utilizat numai pentru limbile care utilizează un octet dublu pentru a reprezenta fiecare caracter, cum ar fi chineza, rusa și japoneza.
Dacă acest parametru este setat la „Adevărat”, atunci Găsire va potrivi numai caractere cu doi octeți cu caractere cu doi octeți. Dacă parametrul este setat la „False”, atunci un caracter cu doi octeți se va potrivi cu caracterele cu un singur sau cu doi octeți.
Parametru SearchFormat
The Parametru SearchFormat vă permite să căutați formate de celule potrivite. Acesta poate fi un anumit font utilizat, sau un font aldin sau o culoare a textului. Înainte de a utiliza acest parametru, trebuie să setați formatul necesar pentru căutare utilizând proprietatea Application.FindFormat.
Iată un exemplu de utilizare:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = AdevăratSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfApplication.FindFormat.ClearSfârșitul Sub |
În acest exemplu, FindFormat proprietatea este setată să caute un font îndrăzneț. Declarația Find apoi caută cuvântul „încălzire” setând parametrul SearchFormat la True, astfel încât să returneze o instanță a textului respectiv numai dacă fontul este aldin.
În exemplele de date ale foii de lucru prezentate mai devreme, acesta va returna A9, care este singura celulă care conține cuvântul „căldură” într-un font aldin.
Asigurați-vă că proprietatea FindFormat este ștearsă la sfârșitul codului. În caz contrar, următoarea căutare va lua în considerare acest lucru și va oferi rezultate incorecte.
În cazul în care utilizați un parametru SearchFormat, puteți utiliza și un wildcard (*) ca valoare de căutare. În acest caz, va căuta orice valoare cu un font aldin:
| 1 | Setați MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Utilizarea mai multor parametri
Toți parametrii de căutare discutați aici pot fi folosiți în combinație, dacă este necesar.
De exemplu, puteți combina parametrul „LookIn” cu parametrul „MatchCase”, astfel încât să priviți întregul text al celulei, dar este sensibil la majuscule
| 123456789 | Sub TestMultipleParameters ()Dim MyRange As RangeSetați MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Dacă nu MyRange nu este nimic atunciMsgBox MyRange.AddressAltfelMsgBox "Not found"End IfSfârșitul Sub |
În acest exemplu, codul va returna A4, dar dacă am folosi doar o parte a textului, de ex. „Căldură”, nu s-ar găsi nimic pentru că suntem potrivite pentru întreaga valoare a celulei. De asemenea, ar eșua din cauza cazului care nu se potrivește.
| 1 | Setați MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Înlocuiți în Excel VBA
Există, după cum vă așteptați, o funcție de înlocuire în Excel VBA, care funcționează într-un mod foarte similar cu „Găsiți”, dar înlocuiește valorile de la locația celulei găsite cu o nouă valoare.
Aceștia sunt parametrii pe care îi puteți utiliza într-o instrucțiune de metodă Înlocuire. Acestea funcționează exact în același mod ca și pentru instrucțiunea metodei Find. Singura diferență față de „Găsiți” este că trebuie să specificați un parametru de înlocuire.
| Nume | Tip | Descriere | Valori |
| Ce | Necesar | Valoarea de căutat | Orice tip de date, cum ar fi un șir sau numeric |
| Înlocuire | Necesar | Șirul de înlocuire. | Orice tip de date, cum ar fi un șir sau numeric |
| Uita-te la | Opțional | Potriviți o parte sau întreaga celulă | xlPart sau xlWhole |
| SearchOrder | Opțional | Ordinea de căutare în - Rânduri sau Coloane | xlByRows sau xlByColumns |
| MatchCase | Opțional | Căutarea este diferențiată de majuscule sau minuscule | Adevărat sau fals |
| MatchByte | Opțional | Folosit numai dacă ați instalat suport de limbă dublu octet | Adevărat sau fals |
| SearchFormat | Opțional | Permite căutarea după formatul celulei | Adevărat sau fals |
| ReplaceFormat | Opțional | Formatul de înlocuire pentru metodă. | Adevărat sau fals |
Parametrul Înlocuiește formatul caută o celulă cu un anumit format, de ex. bold în același mod în care parametrul SearchFormat operează în metoda Find. Mai întâi trebuie să setați proprietatea Application.FindFormat, așa cum se arată în codul de exemplu Găsire prezentat anterior
Înlocuiți fără parametrii opționali
Cel mai simplu, trebuie doar să specificați ce căutați și cu ce doriți să îl înlocuiți.
| 123 | Sub TestReplace ()Foi ("Sheet1"). UsedRange.Replace What: = "Light & Heat", Înlocuire: = "L & H"Sfârșitul Sub |
Rețineți că metoda Găsire va returna prima instanță a valorii potrivite, în timp ce metoda Înlocuire funcționează prin întregul interval specificat și înlocuiește tot ceea ce găsește o potrivire.
Codul de înlocuire afișat aici va înlocui fiecare instanță din „Light & Heat” cu „L & H” prin întreaga gamă de celule definite de obiectul UsedRange
Utilizarea VBA pentru a găsi sau a înlocui textul într-un șir de text VBA
Exemplele de mai sus funcționează excelent atunci când utilizați VBA pentru a interacționa cu datele Excel. Cu toate acestea, pentru a interacționa cu șirurile VBA, puteți utiliza funcții VBA încorporate, cum ar fi INSTR și REPLACE.
Puteți utiliza Funcția INSTR pentru a localiza un șir de text într-un șir mai lung.
| 123 | Sub TestInstr ()MsgBox InStr („Acesta este șirul MyText”, „MyText”)Sfârșitul Sub |
Acest exemplu de cod va returna valoarea 9, care este poziția numerică în care se găsește „MyText” în șirul de căutat.
Rețineți că este sensibil la majuscule și minuscule. Dacă „MyText” este minusculă, atunci va fi returnată o valoare 0, ceea ce înseamnă că șirul de căutare nu a fost găsit. Mai jos vom discuta despre cum să dezactivăm sensibilitatea la majuscule.
INSTR - Începeți
Există doi alți parametri opționali disponibili. Puteți specifica punctul de pornire pentru căutare:
| 1 | MsgBox InStr (9, „Acesta este șirul MyText”, „MyText”) |
Punctul de pornire este specificat ca 9, deci va reveni în continuare 9. Dacă punctul de pornire ar fi 10, atunci s-ar întoarce 0 (fără potrivire), deoarece punctul de pornire ar fi prea departe.
INSTR - Sensibilitate la majuscule
De asemenea, puteți seta un parametru Comparare la vbBinaryCompare sau vbTextCompare. Dacă setați acest parametru, instrucțiunea trebuie să aibă o valoare a parametrului de pornire.
- vbBinaryCompare - Sensibil la majuscule (implicit)
- vbTextCompare - Nu este sensibil la majuscule
| 1 | MsgBox InStr (1, „Acesta este șirul MyText”, „textul meu”, vbTextCompare) |
Această declarație va returna în continuare 9, chiar dacă textul căutării este cu litere mici.
Pentru a dezactiva sensibilitatea la majuscule și minuscule, puteți declara opțiunea Comparare text în partea de sus a modulului de cod.
Funcția de înlocuire VBA
Dacă doriți să înlocuiți caracterele dintr-un șir cu text diferit în codul dvs., atunci metoda Înlocuire este ideală pentru aceasta:
| 123 | Sub TestReplace ()MsgBox Replace („Acesta este șirul MyText”, „MyText”, „Textul meu”)Sfârșitul Sub |
Acest cod înlocuiește „Textul meu” cu „Textul meu”. Rețineți că șirul de căutare este diferențiat de majuscule și minuscule, deoarece o comparație binară este implicită.
De asemenea, puteți adăuga alți parametri opționali:
- start - definește poziția în șirul inițial de la care trebuie să înceapă înlocuitorul. Spre deosebire de metoda Find, acesta returnează un șir trunchiat începând de la numărul de caractere definit de parametrul Start.
- Numara - definește numărul de înlocuiri care trebuie efectuate. În mod implicit, Înlocuire va schimba fiecare instanță a textului de căutare găsit, dar puteți să o limitați la un singur înlocuitor setând parametrul Număr la 1
- Comparaţie - ca și în metoda Găsiți puteți specifica o căutare binară sau o căutare text utilizând vbBinaryCompare sau vbTextCompare. Binarul este diferențiat de majuscule și minuscule, iar textul este diferit de majuscule și minuscule
| 1 | MsgBox Replace („Acesta este șirul MyText (textul meu)”, „Textul meu”, „Textul meu”, 9, 1, vbTextCompare) |
Acest cod returnează „Șirul meu de text (textul meu)”. Acest lucru se datorează faptului că punctul de pornire dat este 9, astfel încât noul șir returnat începe de la caracterul 9. Numai primul „MyText” a fost modificat deoarece parametrul Count este setat la 1.
Metoda Înlocuiește este ideală pentru rezolvarea problemelor precum numele oamenilor care conțin apostrofe de ex. O’Flynn. Dacă utilizați ghilimele simple pentru a defini o valoare a șirului și există un apostrof, aceasta va provoca o eroare deoarece codul va interpreta apostroful ca fiind sfârșitul șirului și nu va recunoaște restul șirului.
Puteți utiliza metoda Înlocuire pentru a înlocui apostroful cu nimic, înlăturându-l complet.
