Tento tutoriál předvede, jak používat metody Najít a nahradit v aplikaci Excel VBA.
Najít VBA
Excel má vynikající vestavěný Nalézt a Najít a nahradit nástroje.
Lze je aktivovat pomocí zkratek CTRL + F (Najít) nebo CTRL + H (Nahradit) nebo prostřednictvím pásu karet: Domů> Úpravy> Najít a vybrat.
Kliknutím Možnosti, můžete zobrazit pokročilé možnosti vyhledávání:
K metodám Najít a Nahradit můžete snadno přistupovat pomocí VBA. Tyto vestavěné metody jsou mnohem rychlejší než cokoli, co byste ve VBA mohli sami napsat.
Najděte příklad VBA
Abychom předvedli funkci Najít, vytvořili jsme následující sadu dat v List1.
Pokud chcete pokračovat, zadejte data do vlastního sešitu.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Hledání VBA bez volitelných parametrů
Při použití metody VBA Find existuje mnoho volitelných parametrů, které můžete nastavit.
Důrazně doporučujeme definovat všechny parametry při každém použití metody Najít!
Pokud nedefinujete volitelné parametry, VBA použije aktuálně vybrané parametry v okně Hledat v Excelu. To znamená, že možná nevíte, jaké parametry vyhledávání se používají při spuštění kódu. Najít lze spustit na celém sešitu nebo listu. Mohlo by to hledat vzorce nebo hodnoty. Neexistuje žádný způsob, jak to zjistit, pokud ručně nezkontrolujete, co je aktuálně vybráno v okně Hledat v Excelu.
Pro jednoduchost začneme příkladem bez definovaných volitelných parametrů.
Jednoduchý příklad hledání
Podívejme se na jednoduchý příklad Najít:
| 123456789 | Dílčí testovací nález ()Dim MyRange As RangeNastavit MyRange = Listy ("List1"). UsedRange.Find ("zaměstnanec")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Tento kód hledá „zaměstnance“ v použitém rozsahu listu 1. Pokud najde „zaměstnance“, přiřadí první nalezený rozsah proměnné rozsahu MyRange.
Dále se zobrazí pole se zprávou s adresou, sloupcem a řádkem nalezeného textu.
V tomto příkladu je použito výchozí nastavení Hledat (za předpokladu, že nebylo změněno v okně Hledat v Excelu):
- Hledaný text je částečně shodný s hodnotou buňky (není vyžadována přesná shoda buňky)
- Při hledání se nerozlišují malá a velká písmena.
- Find pouze prohledá jeden list
Tato nastavení lze změnit pomocí různých volitelných parametrů (popsáno níže).
Najděte Poznámky k metodě
- Najít nevybere buňku, kde se nachází text. Identifikuje pouze nalezený rozsah, se kterým můžete ve svém kódu manipulovat.
- Metoda Find vyhledá pouze první nalezenou instanci.
- Můžete použít zástupné znaky (*), např. hledat „E*“
Nic nalezeno
Pokud hledaný text neexistuje, zůstane objekt rozsahu prázdný. To způsobí zásadní problém, když se váš kód pokusí zobrazit hodnoty umístění, protože neexistují. Výsledkem bude chybová zpráva, kterou nechcete.
Naštěstí můžete testovat prázdný objekt v rámci VBA pomocí Is Operator:
| 1 | Pokud není MyRange nic, pak |
Přidání kódu do našeho předchozího příkladu:
| 12345678910111213 | Dílčí testovací nález ()Dim MyRange As RangeNastavit MyRange = Listy ("List1"). UsedRange.Find ("zaměstnanec")Pokud není MyRange nic, pakMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
Najděte parametry
Doposud jsme se podívali pouze na základní příklad použití metody Najít. Existuje však řada volitelných parametrů, které vám pomohou upřesnit vyhledávání
| Parametr | Typ | Popis | Hodnoty |
| Co | Požadované | Hodnota, kterou chcete vyhledat | Jakýkoli datový typ, například řetězec nebo číslice |
| Po | Volitelný | Odkaz na jednu buňku pro zahájení hledání | Adresa buňky |
| Podívejte se do | Volitelný | Pro vyhledávání použijte vzorce, hodnoty, komentáře | xlValues, xlFormulas, xlComments |
| Podívat se na | Volitelný | Shoda části nebo celé buňky | xlCelá, xlČást |
| Objednávka vyhledávání | Volitelný | Pořadí vyhledávání v řádcích nebo sloupcích | xlByRows, xlByColummns |
| SearchDirection | Volitelný | Směr, kterým má vyhledávání jít - vpřed nebo vzad | xlDalší, xlPředchozí |
| Shodný případ | Volitelný | Hledání rozlišuje velká a malá písmena nebo ne | Pravda nebo lež |
| MatchByte | Volitelný | Používá se pouze v případě, že máte nainstalovanou podporu dvoubajtového jazyka, např. Čínský jazyk | Pravda nebo lež |
| SearchFormat | Volitelný | Povolit vyhledávání podle formátu buňky | Pravda nebo lež |
Po parametru a nalezení více hodnot
Používáte Po parametru k určení počáteční buňky pro vaše vyhledávání. To je užitečné tam, kde existuje více než jedna instance hodnoty, kterou hledáte.
Pokud vyhledávání již našlo jednu hodnotu a víte, že bude nalezeno více hodnot, použijte metodu Find s parametrem „After“ k zaznamenání první instance a poté tuto buňku použijete jako výchozí bod pro další hledání.
Toho můžete využít k vyhledání více instancí hledaného textu:
| 123456789101112131415161718192021222324252627282930313233343536 | Dílčí testMultipleFinds ()Dim MyRange As Range, OldRange As Range, FindStr As String„Podívejte se na první instanci“ „Světlo a teplo“Nastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat")„Pokud není nalezen, odejdiPokud MyRange není nic, ukončete dílčí„Zobrazit první nalezenou adresuMsgBox MyRange.Address`` Vytvořte kopii objektu rozsahuNastavit OldRange = MyRange'Přidejte adresu do řetězce oddělujícího „|“ charakterFindStr = FindStr & "|" & MyRange.Address"Procházejte rozsah a hledejte další instance."DělatVyhledejte „Světlo a teplo“ pomocí předchozí nalezené adresy jako parametru AfterNastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))„Pokud již byla adresa nalezena, ukončete smyčku do - tím se nepřetržité smyčky zastavíIf InStr (FindStr, MyRange.Address) Then Exit Do'Zobrazit poslední nalezenou adresuMsgBox MyRange.Address'Přidejte do řetězce adres nejnovější adresu.'FindStr = FindStr & "|" & MyRange.Address'vytvořit kopii aktuálního rozsahu.'Nastavit OldRange = MyRangeSmyčkaEnd Sub |
Tento kód bude iterovat použitý rozsah a zobrazí adresu pokaždé, když najde instanci „Light & Heat“
Všimněte si, že kód bude pokračovat ve smyčce, dokud nebude v FindStr nalezena duplicitní adresa, v takovém případě opustí smyčku Do.
Parametr LookIn
Můžete použít Parametr LookIn určete, ve které složce buňky chcete hledat. V buňce můžete zadat hodnoty, vzorce nebo komentáře.
- xlHodnoty - Hledá hodnoty buněk (konečná hodnota buňky po jejím výpočtu)
- xl Vzorce - Vyhledává v samotném vzorci buňky (cokoli je do buňky zadáno)
- xlKomentáře - Vyhledává v poznámkách k buňkám
- xlCommentsThreaded - Vyhledávání v komentářích buněk
Za předpokladu, že byl do listu zadán vzorec, můžete pomocí tohoto ukázkového kódu najít první umístění libovolného vzorce:
| 12345678910 | Dílčí testovací pohled ()Dim MyRange As RangeNastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
Pokud byl parametr „LookIn“ nastaven na xlValues, kód by zobrazil zprávu „Nenalezeno“. V tomto případě vrátí B10.
Použití parametru LookAt
The Parametr LookAt určuje, zda find vyhledá přesnou shodu buněk, nebo vyhledá libovolnou buňku obsahující hledanou hodnotu.
- xlCelá - Vyžaduje, aby celá buňka odpovídala hledané hodnotě
- xlČást - Hledá v buňce hledaný řetězec
Tento příklad kódu vyhledá první buňku obsahující text „light“. S Lookat: = xlPart, vrátí shodu pro „Light & Heat“.
| 123456789 | Dílčí testLookAt ()Dim MyRange As RangeNastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
Li xlCelá byla nastavena, shoda se vrátí pouze v případě, že hodnota buňky byla „světlá“.
Parametr SearchOrder
The Parametr SearchOrder diktuje, jak bude vyhledávání prováděno v celém rozsahu.
- xlŘádky - Hledání se provádí řádek po řádku
- xlXolumns - Hledání se provádí sloupec po sloupci
| 123456789 | Sub TestSearchOrder ()Dim MyRange As RangeNastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("employee", SearchOrder: = xlColumns)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
To ovlivňuje, která shoda bude nalezena jako první.
Pomocí testovacích dat zadaných dříve do listu, když jsou pořadí hledání sloupce, je umístěná buňka A5. Když je parametr pořadí hledání změněn na xlRows, umístěná buňka je C4
To je důležité, pokud máte v rozsahu vyhledávání duplicitní hodnoty a chcete najít první instanci pod konkrétním názvem sloupce.
Parametr SearchDirection
The Parametr SearchDirection určuje, jakým směrem se bude vyhledávání ubírat - efektivně dopředu nebo dozadu.
- xlDalší - Vyhledejte další odpovídající hodnotu v rozsahu
- xlPředchozí - Vyhledejte předchozí odpovídající hodnotu v rozsahu
Opět platí, že pokud jsou v rozsahu vyhledávání duplicitní hodnoty, může to mít vliv na to, která se najde jako první.
| 12345678910 | Sub TestSearchDirection ()Dim MyRange As RangeNastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", SearchDirection: = xlPrevious)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
Pomocí tohoto kódu v testovacích datech vrátí směr vyhledávání xlPrevious umístění C9. Pomocí parametru xlNext se vrátí umístění formátu A4.
Parametr Další znamená, že hledání začne v levém horním rohu rozsahu vyhledávání a bude směřovat dolů. Parametr Předchozí znamená, že vyhledávání začne v pravém dolním rohu rozsahu vyhledávání a bude pracovat směrem nahoru.
Parametr MatchByte
The Parametr MatchBye se používá pouze pro jazyky, které pro zobrazení každého znaku používají dvoubajt, jako je čínština, ruština a japonština.
Pokud je tento parametr nastaven na „True“, pak funkce Najít bude odpovídat pouze dvoubajtovým znakům a dvoubajtovým znakům. Pokud je parametr nastaven na „False“, pak se dvoubajtový znak bude shodovat s jednobarevnými nebo dvoubajtovými znaky.
Parametr SearchFormat
The Parametr SearchFormat umožňuje vyhledávat odpovídající formáty buněk. Může to být konkrétní použité písmo, tučné písmo nebo barva textu. Před použitím tohoto parametru musíte nastavit požadovaný formát pro vyhledávání pomocí vlastnosti Application.FindFormat.
Zde je příklad použití:
| 12345678910111213 | Sub TestSearchFormat ()Dim MyRange As RangeApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfApplication.FindFormat.ClearEnd Sub |
V tomto případě FindFormat vlastnost je nastavena tak, aby hledala tučné písmo. Příkaz Najít poté vyhledá slovo „teplo“ a nastaví parametr SearchFormat na hodnotu True, takže vrátí instanci tohoto textu pouze v případě, že je písmo tučné.
V ukázkových datech listu zobrazených dříve vrátí A9, což je jediná buňka obsahující slovo „teplo“ tučným písmem.
Ujistěte se, že vlastnost FindFormat je na konci kódu vymazána. Pokud tak neučiníte, vaše další vyhledávání to bude stále brát v úvahu a vrátí nesprávné výsledky.
Pokud používáte parametr SearchFormat, můžete jako hodnotu vyhledávání použít také zástupný znak (*). V tomto případě vyhledá libovolnou hodnotu tučným písmem:
| 1 | Nastavit MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Použití více parametrů
Všechny zde diskutované parametry vyhledávání lze v případě potřeby použít ve vzájemné kombinaci.
Můžete například zkombinovat parametr „LookIn“ s parametrem „MatchCase“, abyste se podívali na celý text buňky, ale rozlišují se velká a malá písmena
| 123456789 | Dílčí test Vícenásobné parametry ()Dim MyRange As RangeNastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", LookAt: = xlWhole, MatchCase: = True)Pokud není MyRange nic, pakMsgBox MyRange.AddressJinýMsgBox "Nenalezeno"Konec IfEnd Sub |
V tomto případě kód vrátí A4, ale pokud bychom použili pouze část textu, např. „Teplo“, nic by se nenašlo, protože se shodujeme v celé hodnotě buňky. Také by selhalo, protože případ neodpovídal.
| 1 | Nastavte MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Nahradit v aplikaci Excel VBA
Jak můžete očekávat, v aplikaci Excel VBA existuje funkce Nahradit, která funguje velmi podobným způsobem jako „Najít“, ale nahrazuje hodnoty v nalezeném umístění buňky novou hodnotou.
Toto jsou parametry, které můžete použít v příkazu metody Replace. Ty fungují přesně stejným způsobem jako pro příkaz Find metody. Jediným rozdílem oproti „Find“ je, že musíte zadat parametr Replacement.
| název | Typ | Popis | Hodnoty |
| Co | Požadované | Hodnota, kterou chcete vyhledat | Jakýkoli datový typ, například řetězec nebo číslice |
| Výměna, nahrazení | Požadované | Náhradní řetězec. | Jakýkoli datový typ, například řetězec nebo číslice |
| Podívat se na | Volitelný | Shoda části nebo celé buňky | xlPart nebo xlWhole |
| Hledat Objednávka | Volitelný | Pořadí vyhledávání v řádcích nebo sloupcích | xlByRows nebo xlByColumns |
| Shodný případ | Volitelný | Hledání rozlišuje velká a malá písmena nebo ne | Pravda nebo lež |
| MatchByte | Volitelný | Používá se pouze v případě, že máte nainstalovanou podporu dvoubajtového jazyka | Pravda nebo lež |
| SearchFormat | Volitelný | Povolit vyhledávání podle formátu buňky | Pravda nebo lež |
| ReplaceFormat | Volitelný | Formát nahrazení metody. | Pravda nebo lež |
Parametr Nahradit formát hledá buňku s určitým formátem, např. tučně stejným způsobem, jakým parametr SearchFormat funguje v metodě Find. Nejprve musíte nastavit vlastnost Application.FindFormat, jak je ukázáno v ukázkovém kódu Find zobrazeném dříve
Nahradit bez volitelných parametrů
Nejjednodušeji stačí zadat, co hledáte a čím ho chcete nahradit.
| 123 | Dílčí test Nahradit ()Listy ("List1"). UsedRange.Replace What: = "Light & Heat", Replacement: = "L & H"End Sub |
Všimněte si, že metoda Find vrátí pouze první instanci odpovídající hodnoty, zatímco Replace metoda funguje v celém zadaném rozsahu a nahradí vše, u čeho najde shodu.
Zde zobrazený náhradní kód nahradí všechny instance „Light & Heat“ za „L & H“ v celém rozsahu buněk definovaných objektem UsedRange
Použití VBA k nalezení nebo nahrazení textu v textovém řetězci VBA
Výše uvedené příklady fungují skvěle při použití VBA k interakci s daty Excelu. K interakci s řetězci VBA však můžete použít vestavěné funkce VBA jako INSTR a REPLACE.
Můžete použít Funkce INSTR k vyhledání řetězce textu v rámci delšího řetězce.
| 123 | Dílčí testInstr ()MsgBox InStr („Toto je řetězec MyText“, „MyText“)End Sub |
Tento ukázkový kód vrátí hodnotu 9, což je pozice čísla, kde se v hledaném řetězci nachází „MyText“.
Všimněte si, že rozlišují velká a malá písmena. Pokud je „MyText“ malými písmeny, bude vrácena hodnota 0, což znamená, že vyhledávací řetězec nebyl nalezen. Níže budeme diskutovat o tom, jak zakázat rozlišování malých a velkých písmen.
INSTR - Začněte
K dispozici jsou další dva volitelné parametry. Můžete zadat počáteční bod pro vyhledávání:
| 1 | MsgBox InStr (9, "This is MyText string", "MyText") |
Počáteční bod je zadán jako 9, takže se stále vrátí 9. Pokud byl počáteční bod 10, pak by se vrátil 0 (bez shody), protože počáteční bod by byl příliš daleko dopředu.
INSTR - Rozlišování malých a velkých písmen
Můžete také nastavit parametr Porovnat na vbBinaryCompare nebo vbTextCompare. Pokud nastavíte tento parametr, příkaz musí mít počáteční hodnotu parametru.
- vbBinaryCompare - Rozlišuje velká a malá písmena (výchozí)
- vbTextCompare - Nerozlišuje velká a malá písmena
| 1 | MsgBox InStr (1, "This is MyText string", "mytext", vbTextCompare) |
Toto prohlášení bude stále vracet 9, i když je hledaný text malými písmeny.
Chcete-li zakázat rozlišování malých a velkých písmen, můžete také deklarovat text pro porovnání možností v horní části modulu kódu.
Funkce nahrazení VBA
Pokud chcete v kódu nahradit znaky v řetězci jiným textem, je metoda Replace ideální pro toto:
| 123 | Dílčí test Nahradit ()MsgBox Replace ("This is MyText string", "MyText", "My Text")End Sub |
Tento kód nahrazuje „MyText“ textem „My Text“. Všimněte si, že vyhledávací řetězec rozlišuje velká a malá písmena, protože binární porovnání je výchozí.
Můžete také přidat další volitelné parametry:
- Start - definuje pozici v počátečním řetězci, ze kterého má náhrada začít. Na rozdíl od Find metody vrací zkrácený řetězec počínaje číslem znaku definovaným parametrem Start.
- Počet - definuje počet výměn, které mají být provedeny. Ve výchozím nastavení nahradí Nahradit každou instanci nalezeného vyhledávacího textu, ale můžete to omezit na jedinou náhradu nastavením parametru Počet na 1
- Porovnat - jako v metodě Najít můžete zadat binární vyhledávání nebo textové vyhledávání pomocí vbBinaryCompare nebo vbTextCompare. Binární rozlišují malá a velká písmena a text nerozlišuje velká a malá písmena
| 1 | MsgBox Replace ("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Tento kód vrací „Můj textový řetězec (mytext)“. Důvodem je zadaný počáteční bod 9, takže nový vrácený řetězec začíná znakem 9. Byl změněn pouze první „MyText“, protože parametr Count je nastaven na 1.
Metoda Replace je ideální pro řešení problémů, jako jsou jména lidí obsahující apostrofy, např. O'Flynn. Pokud k definování hodnoty řetězce používáte jednoduché uvozovky a existuje apostrof, způsobí to chybu, protože kód apostrof interpretuje jako konec řetězce a zbytek řetězce nerozpozná.
Metodu Replace můžete použít k nahrazení apostrofu ničím, jeho úplným odstraněním.
