Tento tutoriál ukáže, ako používať metódy Hľadať a nahradiť v programe Excel VBA.
Nájdenie VBA
Excel má vynikajúcu vstavanú funkciu Nájsť a Nájsť a nahradiť nástroje.
Dajú sa aktivovať pomocou skratiek CTRL + F (Nájsť) alebo CTRL + H (Nahradiť) alebo prostredníctvom pásky: Domov> Úpravy> Nájsť a vybrať.
Kliknutím možnosti, môžete vidieť rozšírené možnosti vyhľadávania:
Pomocou VBA môžete ľahko pristupovať k metódam Hľadať aj Nahradiť. Tieto vstavané metódy sú oveľa rýchlejšie ako čokoľvek, čo by ste mohli sami napísať vo VBA.
Nájdite príklad VBA
Aby sme demonštrovali funkciu Hľadať, vytvorili sme v hárku1 nasledujúcu množinu údajov.
Ak chcete pokračovať, zadajte údaje do vlastného zošita.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Hľadanie VBA bez voliteľných parametrov
Pri použití metódy VBA Find existuje mnoho voliteľných parametrov, ktoré môžete nastaviť.
Pri použití metódy Hľadať dôrazne odporúčame definovať všetky parametre!
Ak nedefinujete voliteľné parametre, VBA použije aktuálne vybrané parametre v okne Hľadať v Exceli. To znamená, že pri spustení kódu možno nebudete vedieť, aké parametre vyhľadávania sa používajú. Hľadanie je možné spustiť na celom zošite alebo na hárku. Mohlo by vyhľadávať vzorce alebo hodnoty. Neexistuje žiadny spôsob, ako to vedieť, pokiaľ ručne nekontrolujete, čo je aktuálne vybraté v okne Hľadať v programe Excel.
Pre jednoduchosť začneme príkladom, v ktorom nie sú definované žiadne voliteľné parametre.
Jednoduchý príklad hľadania
Pozrime sa na jednoduchý príklad Hľadať:
| 123456789 | Sub TestFind ()Dim MyRange As RangeNastaviť MyRange = Listy ("List1"). UsedRange.Find ("zamestnanec")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowKoniec pod |
Tento kód hľadá „zamestnanca“ v použitom rozsahu hárku 1. Ak nájde „zamestnanca“, priradí prvý nájdený rozsah premennej rozsahu MyRange.
Ďalej sa zobrazia polia so správou s adresou, stĺpcom a riadkom nájdeného textu.
V tomto prípade sú použité predvolené nastavenia Hľadania (za predpokladu, že neboli zmenené v okne Hľadať v Exceli):
- Hľadaný text je čiastočne zhodný s hodnotou bunky (presná zhoda bunky sa nevyžaduje)
- Pri vyhľadávaní sa nerozlišujú malé a veľké písmená.
- Hľadať vyhľadáva iba jeden pracovný hárok
Tieto nastavenia je možné zmeniť pomocou rôznych voliteľných parametrov (popísané nižšie).
Nájdite poznámky k metóde
- Hľadať nevyberie bunku, kde sa nachádza text. Identifikuje iba nájdený rozsah, s ktorým môžete vo svojom kóde manipulovať.
- Metóda Find vyhľadá iba prvú nájdenú inštanciu.
- Zástupné znaky (*) môžete použiť napr. hľadať „E*“
nič nebolo nájdené
Ak hľadaný text neexistuje, objekt rozsahu zostane prázdny. To spôsobuje veľký problém, keď sa váš kód pokúša zobraziť hodnoty umiestnenia, pretože neexistujú. Výsledkom bude chybové hlásenie, ktoré nechcete.
Našťastie môžete v rámci VBA testovať objekt prázdneho rozsahu pomocou operátora Is:
| 1 | Ak nie je MyRange nič, potom |
Pridanie kódu do nášho predchádzajúceho príkladu:
| 12345678910111213 | Sub TestFind ()Dim MyRange As RangeNastaviť MyRange = Listy ("List1"). UsedRange.Find ("zamestnanec")Ak nie je MyRange nič, potomMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
Nájdite parametre
Doposiaľ sme sa pozreli iba na základný príklad použitia metódy Nájsť. Existuje však množstvo voliteľných parametrov, ktoré vám pomôžu spresniť vyhľadávanie
| Parameter | Napíšte | Popis | Hodnoty |
| Čo | Požadovaný | Hodnota, ktorú treba hľadať | Akýkoľvek typ údajov, napríklad reťazec alebo číslica |
| Po | Voliteľné | Odkaz na jednu bunku na spustenie vyhľadávania | Adresa bunky |
| Pozrieť sa do | Voliteľné | Na vyhľadávanie používajte vzorce, hodnoty, komentáre | xlValues, xlFormulas, xlComments |
| Pozri na | Voliteľné | Priraďte časť alebo celú bunku | xlCelá, xlČasť |
| Objednávka vyhľadávania | Voliteľné | Poradie na vyhľadávanie - riadky alebo stĺpce | xlByRows, xlByColummns |
| SearchDirection | Voliteľné | Smer hľadania, ktorým sa má ísť - dopredu alebo dozadu | xlĎalej, xlPredchádzajúce |
| MatchCase | Voliteľné | Pri hľadaní sa rozlišujú malé a veľké písmena alebo nie | Pravda alebo lož |
| MatchByte | Voliteľné | Používa sa iba vtedy, ak máte nainštalovanú podporu dvojbajtového jazyka, napr. Čínsky jazyk | Pravda alebo lož |
| SearchFormat | Voliteľné | Povoliť vyhľadávanie podľa formátu bunky | Pravda alebo lož |
Po parametri a vyhľadajte viac hodnôt
Používate Po parametri na zadanie počiatočnej bunky pre vaše vyhľadávanie. Je to užitočné tam, kde existuje viac ako jedna inštancia hodnoty, ktorú hľadáte.
Ak vyhľadávanie už našlo jednu hodnotu a viete, že sa nájde viac hodnôt, použite metódu Find s parametrom „After“ na zaznamenanie prvej inštancie a potom túto bunku použite ako počiatočný bod pre ďalšie vyhľadávanie.
Toto môžete použiť na nájdenie viacerých inštancií hľadaného textu:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String„Vyhľadajte prvý prípad položky„ Svetlo a teplo “Nastaviť MyRange = Listy („List1“). UsedRange.Find („Svetlo a teplo“)„Ak sa nenájde, odíďAk MyRange nie je nič, ukončite podponuku'Zobrazená prvá nájdená adresaMsgBox MyRange.Address„Vytvorte kópiu objektu rozsahuNastaviť OldRange = MyRange'Pridajte adresu do reťazca oddeľujúceho „|“ charakterFindStr = FindStr & "|" & MyRange.Address„Opakujte v rozsahu a hľadajte ďalšie inštancieUrob„Hľadajte„ Svetlo a teplo “pomocou predchádzajúcej nájdenej adresy ako parametra AfterNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))„Ak je adresa už nájdená, ukončite cyklus do - toto zastaví nepretržité opakovanieAk InStr (FindStr, MyRange.Address), potom Ukončiť Do'Zobraziť poslednú nájdenú adresuMsgBox MyRange.Address'Do reťazca adries pridajte najnovšiu adresuFindStr = FindStr & "|" & MyRange.Address'vytvorte kópiu aktuálneho rozsahuNastaviť OldRange = MyRangeSlučkaKoniec pod |
Tento kód bude iterovať použitý rozsah a adresu zobrazí vždy, keď nájde inštanciu „Light & Heat“
Všimnite si toho, že kód sa bude opakovať, kým sa v FindStr nenájde duplicitná adresa, v takom prípade opustí slučku Do.
Parameter LookIn
Môžete použiť Parameter LookIn Ak chcete určiť, v ktorej časti bunky chcete hľadať. V bunke môžete zadať hodnoty, vzorce alebo komentáre.
- xlHodnoty - Hľadá hodnoty buniek (konečná hodnota bunky po jej výpočte)
- xl Vzorce - Vyhľadáva sa v samotnom vzorci bunky (čokoľvek je do bunky zadané)
- xlKomentáre - Vyhľadávanie v poznámkach k bunke
- xlKomentkyVlákno - Vyhľadávanie v komentároch k bunke
Za predpokladu, že bol do pracovného hárka zadaný vzorec, môžete pomocou tohto ukážkového kódu nájsť prvé umiestnenie akéhokoľvek vzorca:
| 12345678910 | Sub TestLookIn ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
Ak by bol parameter „LookIn“ nastavený na xlValues, kód by zobrazil správu „Nenašlo sa“. V tomto prípade vráti B10.
Použitie parametra LookAt
The Parameter LookAt určuje, či funkcia find vyhľadá presnú zhodu buniek alebo vyhľadá ľubovoľnú bunku obsahujúcu hľadanú hodnotu.
- xlCelá - Vyžaduje, aby sa celá bunka zhodovala s hľadanou hodnotou
- xlČasť - Hľadá v bunke hľadaný reťazec
Tento príklad kódu vyhľadá prvú bunku obsahujúcu text „light“. S Lookat: = xlČasť, vráti zápas pre „Light & Heat“.
| 123456789 | Sub TestLookAt ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
Ak xlCelá bola nastavená, zhoda sa vráti iba vtedy, ak je hodnota bunky „svetlá“.
Parameter SearchOrder
The Parameter SearchOrder určuje, ako sa bude vyhľadávanie vykonávať v celom rozsahu.
- xlRows - Vyhľadávanie sa vykonáva po riadkoch
- xlXolóny - Vyhľadávanie sa vykonáva stĺpček po stĺpci
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("employee", SearchOrder: = xlColumns)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
To ovplyvňuje, ktorá zhoda sa nájde ako prvá.
Použitím testovacích údajov zadaných do pracovného hárka skôr, keď sú poradím vyhľadávania stĺpce, umiestnená bunka je A5. Keď sa parameter poradia vyhľadávania zmení na xlRows, umiestnená bunka je C4
Je to dôležité, ak máte v rozsahu vyhľadávania duplicitné hodnoty a chcete nájsť prvú inštanciu pod konkrétnym názvom stĺpca.
Parameter SearchDirection
The Parameter SearchDirection určuje, akým smerom sa bude vyhľadávanie uberať - efektívne dopredu alebo dozadu.
- xlĎalej - Vyhľadajte ďalšiu zodpovedajúcu hodnotu v rozsahu
- xlPredchádzajúce - Vyhľadajte predchádzajúcu zodpovedajúcu hodnotu v rozsahu
Opäť platí, že ak sa v rámci rozsahu vyhľadávania nachádzajú duplicitné hodnoty, môže to mať vplyv na to, ktorá sa najskôr nájde.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
Použitím tohto kódu na testovacie údaje smer vyhľadávania xlPrevious vráti polohu C9. Použitie parametra xlNext vráti umiestnenie A4.
Parameter Next znamená, že vyhľadávanie začne v ľavom hornom rohu rozsahu vyhľadávania a bude pracovať smerom nadol. Parameter Predchádzajúci znamená, že vyhľadávanie sa začne v pravom dolnom rohu rozsahu vyhľadávania a bude postupovať nahor.
Parameter MatchByte
The Parameter MatchBye sa používa iba v jazykoch, ktoré na zobrazenie každého znaku používajú dvojbajt, ako je čínština, ruština a japončina.
Ak je tento parameter nastavený na hodnotu „True“, funkcia Hľadať bude zhodovať iba dvojbajtové znaky s dvojbajtovými znakmi. Ak je parameter nastavený na hodnotu „False“, dvojbajtový znak sa bude zhodovať s jedno alebo dvojbajtovými znakmi.
Parameter SearchFormat
The Parameter SearchFormat umožňuje vyhľadávať zodpovedajúce formáty buniek. Môže to byť konkrétne používané písmo, tučné písmo alebo farba textu. Pred použitím tohto parametra musíte nastaviť požadovaný formát pre vyhľadávanie pomocou vlastnosti Application.FindFormat.
Tu je príklad použitia:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkApplication.FindFormat.ClearKoniec pod |
V tomto prípade je FindFormat vlastnosť je nastavená tak, aby hľadala tučné písmo. Príkaz Find potom vyhľadá slovo „teplo“ a parameter SearchFormat nastaví na hodnotu True, aby vrátil inštanciu tohto textu iba vtedy, ak je písmo tučné.
V ukážkových údajoch pracovného hárka uvedených vyššie vráti A9, čo je jediná bunka obsahujúca slovo „teplo“ hrubým písmom.
Uistite sa, že vlastnosť FindFormat je na konci kódu vymazaná. Ak to neurobíte, vaše ďalšie hľadanie to bude brať do úvahy a vráti nesprávne výsledky.
Tam, kde použijete parameter SearchFormat, môžete ako hodnotu vyhľadávania použiť aj zástupný znak (*). V tomto prípade vyhľadá akúkoľvek hodnotu tučným písmom:
| 1 | Nastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Použitie viacerých parametrov
Všetky parametre vyhľadávania, o ktorých sa tu diskutuje, je možné v prípade potreby použiť navzájom.
Môžete napríklad skombinovať parameter „LookIn“ s parametrom „MatchCase“, aby ste sa pozreli na celý text bunky, ale rozlišujú sa v ňom veľké a malé písmená.
| 123456789 | Dielčí test Viacnásobné parametre ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Ak nie je MyRange nič, potomMsgBox MyRange.AddressInakMsgBox „Nenašlo sa“Koniec AkKoniec pod |
V tomto prípade kód vráti A4, ale ak by sme použili iba časť textu napr. „Teplo“, nič by sa nenašlo, pretože sa zhodujeme v celej hodnote bunky. Tiež by zlyhalo, pretože prípad sa nezhoduje.
| 1 | Nastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Nahradiť v programe Excel VBA
Ako môžete očakávať, v programe Excel VBA je funkcia Nahradiť, ktorá funguje veľmi podobným spôsobom ako „Hľadať“, ale hodnoty v umiestnení bunky nahradí novou hodnotou.
Toto sú parametre, ktoré môžete použiť v príkaze metódy Nahradiť. Fungujú úplne rovnako ako v prípade príkazu Find. Jediným rozdielom oproti funkcii „Nájsť“ je, že musíte zadať parameter nahradenia.
| názov | Napíšte | Popis | Hodnoty |
| Čo | Požadovaný | Hodnota, ktorú treba hľadať | Akýkoľvek typ údajov, napríklad reťazec alebo číslica |
| Výmena | Požadovaný | Náhradný reťazec. | Akýkoľvek typ údajov, napríklad reťazec alebo číslica |
| Pozri na | Voliteľné | Priraďte časť alebo celú bunku | xlČasť alebo xlWhole |
| Objednávka vyhľadávania | Voliteľné | Poradie vyhľadávania v - riadkoch alebo stĺpcoch | xlByRows alebo xlByColumns |
| MatchCase | Voliteľné | Pri hľadaní sa rozlišujú malé a veľké písmena alebo nie | Pravda alebo lož |
| MatchByte | Voliteľné | Používa sa iba vtedy, ak máte nainštalovanú podporu dvojbajtového jazyka | Pravda alebo lož |
| SearchFormat | Voliteľné | Povoliť vyhľadávanie podľa formátu bunky | Pravda alebo lož |
| ReplaceFormat | Voliteľné | Formát nahradenia metódy. | Pravda alebo lož |
Parameter Nahradiť formát hľadá bunku s konkrétnym formátom, napr. tučným písmom rovnakým spôsobom, ako parameter SearchFormat funguje v metóde Hľadať. Najprv musíte nastaviť vlastnosť Application.FindFormat, ako je to uvedené v ukážkovom kóde Find vyššie
Nahradiť bez voliteľných parametrov
Najjednoduchšie je zadať iba to, čo hľadáte a čím ho chcete nahradiť.
| 123 | Náhradný test Nahradiť ()Listy („List1“). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"Koniec pod |
Všimnite si toho, že metóda Hľadať vráti iba prvú inštanciu zodpovedajúcej hodnoty, zatiaľ čo metóda Nahradiť funguje v celom uvedenom rozsahu a nahradí všetko, v čom nájde zhodu.
Tu zobrazený náhradný kód nahradí každú inštanciu „Light & Heat“ za „L & H“ v celom rozsahu buniek definovaných objektom UsedRange
Použitie VBA na nájdenie alebo nahradenie textu v textovom reťazci VBA
Vyššie uvedené príklady fungujú skvele pri použití VBA na interakciu s údajmi programu Excel. Na interakciu so reťazcami VBA však môžete použiť vstavané funkcie VBA, ako sú INSTR a REPLACE.
Môžete použiť Funkcia INSTR na vyhľadanie reťazca textu v dlhšom reťazci.
| 123 | Sub TestInstr ()MsgBox InStr („Toto je reťazec MyText“, „MyText“)Koniec pod |
Tento ukážkový kód vráti hodnotu 9, čo je číselná pozícia, kde sa v hľadanom reťazci nachádza „MyText“.
Upozorňujeme, že rozlišujú sa malé a veľké písmená. Ak je „MyText“ malými písmenami, vráti sa hodnota 0, čo znamená, že hľadaný reťazec nebol nájdený. Ďalej budeme diskutovať o tom, ako zakázať rozlišovanie malých a veľkých písmen.
INSTR - Začnite
K dispozícii sú ďalšie dva voliteľné parametre. Môžete zadať počiatočný bod vyhľadávania:
| 1 | MsgBox InStr (9, „Toto je reťazec MyText“, „MyText“) |
Počiatočný bod je zadaný ako 9, takže sa stále vráti 9. Ak bol počiatočný bod 10, potom by sa vrátil 0 (žiadny zápas), pretože počiatočný bod by bol príliš ďaleko dopredu.
INSTR - Rozlišovanie malých a veľkých písmen
Môžete tiež nastaviť parameter Porovnať na vbBinaryCompare alebo vbTextCompare. Ak nastavíte tento parameter, príkaz musí mať hodnotu počiatočného parametra.
- vbBinaryCompare - Rozlišujú sa malé a veľké písmená (predvolené)
- vbTextCompare - Nerozlišuje malé a veľké písmená
| 1 | MsgBox InStr (1, „Toto je reťazec MyText“, „mytext“, vbTextCompare) |
Tento príkaz stále vráti 9, aj keď je hľadaný text malými písmenami.
Ak chcete zakázať rozlišovanie malých a veľkých písmen, môžete tiež deklarovať text na porovnanie možností v hornej časti modulu kódu.
Funkcia nahradenia VBA
Ak chcete vo svojom kóde nahradiť znaky v reťazci iným textom, je metóda Replace na to ideálna:
| 123 | Náhradný test Nahradiť ()MsgBox Nahradiť („Toto je reťazec MyText“, „MyText“, „Môj text“)Koniec pod |
Tento kód nahrádza text „MyText“ textom „My Text“. Všimnite si toho, že vo vyhľadávacom reťazci sa rozlišujú malé a veľké písmená, pretože binárne porovnanie je predvolené.
Môžete tiež pridať ďalšie voliteľné parametre:
- Začnite - definuje pozíciu v počiatočnom reťazci, od ktorého má náhrada začínať. Na rozdiel od metódy Find vracia skrátený reťazec začínajúci číslom znaku definovaným parametrom Start.
- Gróf - definuje počet výmen, ktoré sa majú vykonať. V predvolenom nastavení funkcia Nahradiť zmení každú inštanciu vyhľadaného textu, ale môžete to obmedziť na jednu náhradu tým, že nastavíte parameter Počet na 1.
- Porovnaj - ako v metóde Hľadať môžete zadať binárne vyhľadávanie alebo textové vyhľadávanie pomocou vbBinaryCompare alebo vbTextCompare. V binárnom formáte sa rozlišujú malé a veľké písmená a v texte sa nerozlišujú malé a veľké písmena
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Tento kód vráti „Môj textový reťazec (mytext)“. Dôvodom je, že počiatočný bod je 9, takže nový vrátený reťazec začína znakom 9. Bol zmenený iba prvý „MyText“, pretože parameter Count je nastavený na 1.
Metóda Nahradiť je ideálna na riešenie problémov, ako sú názvy ľudí obsahujúce apostrofy, napr. O'Flynn. Ak na definovanie hodnoty reťazca použijete jednoduché úvodzovky a existuje apostrof, spôsobí to chybu, pretože kód interpretuje apostrof ako koniec reťazca a zvyšok reťazca nerozpozná.
Metódu Nahradiť môžete použiť na nahradenie apostrofu ničím, jeho úplným odstránením.
