W tym samouczku zademonstrujemy, jak korzystać z metod Znajdź i zamień w programie Excel VBA.
Znajdź VBA
Excel ma doskonałe wbudowane Odnaleźć oraz Znaleźć zamiennik narzędzia.
Można je aktywować za pomocą skrótów CTRL + F (Znajdź) lub CTRL + H (Wymień) lub przez Wstążkę: Strona główna > Edycja > Znajdź i wybierz.
Klikając Opcje, możesz zobaczyć zaawansowane opcje wyszukiwania:
Możesz łatwo uzyskać dostęp do metod Znajdź i Zamień za pomocą VBA. Te wbudowane metody są znacznie szybsze niż wszystko, co możesz napisać samodzielnie w VBA.
Znajdź przykład VBA
Aby zademonstrować funkcję Znajdź, utworzyliśmy następujący zestaw danych w Arkuszu1.
Jeśli chcesz kontynuować, wprowadź dane do własnego skoroszytu.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Wyszukiwanie VBA bez parametrów opcjonalnych
Podczas korzystania z metody VBA Find istnieje wiele opcjonalnych parametrów, które można ustawić.
Zdecydowanie zalecamy zdefiniowanie wszystkich parametrów za każdym razem, gdy używasz metody Find!
Jeśli nie zdefiniujesz opcjonalnych parametrów, VBA użyje aktualnie wybranych parametrów w oknie Znajdź Excela. Oznacza to, że możesz nie wiedzieć, jakie parametry wyszukiwania są używane podczas uruchamiania kodu. Wyszukiwanie można uruchomić na całym skoroszycie lub arkuszu. Może wyszukiwać formuły lub wartości. Nie ma sposobu, aby się dowiedzieć, chyba że ręcznie sprawdzisz, co jest aktualnie wybrane w oknie wyszukiwania programu Excel.
Dla uproszczenia zaczniemy od przykładu bez zdefiniowanych parametrów opcjonalnych.
Prosty przykład znajdowania
Spójrzmy na prosty przykład Find:
| 123456789 | Podtest Znajdź()Przyciemnij mój zakres jako zakresUstaw MójZakres = Arkusze("Arkusz1").UsedRange.Find("pracownik")MsgBox MójZakres.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowNapis końcowy |
Ten kod wyszukuje słowo „pracownik” w używanym zakresie Arkusza1. Jeśli znajdzie „pracownika”, przypisze pierwszy znaleziony zakres do zmiennej zakresu MyRange.
Następnie zostaną wyświetlone pola komunikatów z adresem, kolumną i wierszem znalezionego tekstu.
W tym przykładzie używane są domyślne ustawienia wyszukiwania (zakładając, że nie zostały zmienione w oknie wyszukiwania programu Excel):
- Wyszukiwany tekst jest częściowo dopasowany do wartości komórki (dokładne dopasowanie komórki nie jest wymagane)
- W wyszukiwaniu nie jest rozróżniana wielkość liter.
- Znajdź tylko wyszukiwania w jednym arkuszu
Ustawienia te można zmieniać za pomocą różnych opcjonalnych parametrów (omówionych poniżej).
Znajdź uwagi dotyczące metody
- Znajdź nie zaznacza komórki, w której znajduje się tekst. Identyfikuje tylko znaleziony zakres, którym możesz manipulować w swoim kodzie.
- Metoda Find zlokalizuje tylko pierwsze znalezione wystąpienie.
- Możesz użyć symboli wieloznacznych (*) np. wyszukaj „E*”
nic nie znaleziono
Jeśli wyszukiwany tekst nie istnieje, obiekt zakresu pozostanie pusty. Powoduje to poważny problem, gdy kod próbuje wyświetlić wartości lokalizacji, ponieważ nie istnieją. Spowoduje to wyświetlenie komunikatu o błędzie, którego nie chcesz.
Na szczęście możesz przetestować pusty obiekt zakresu w VBA za pomocą operatora Is:
| 1 | Jeśli nie MyRange to nic, to |
Dodanie kodu do naszego poprzedniego przykładu:
| 12345678910111213 | Podtest Znajdź()Przyciemnij mój zakres jako zakresUstaw MójZakres = Arkusze("Arkusz1").UsedRange.Find("pracownik")Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresMsgBox MyRange.ColumnMsgBox MyRange.RowW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
Znajdź parametry
Do tej pory przyjrzeliśmy się tylko podstawowemu przykładowi użycia metody Find. Dostępnych jest jednak wiele opcjonalnych parametrów, które pomogą Ci zawęzić wyszukiwanie
| Parametr | Rodzaj | Opis | Wartości |
| Co | Wymagany | Wartość do wyszukania | Dowolny typ danych, taki jak ciąg lub liczba |
| Później | Opcjonalny | Odwołanie do pojedynczej komórki, aby rozpocząć wyszukiwanie | Adres komórki |
| LookIn | Opcjonalny | Użyj formuł, wartości, komentarzy do wyszukiwania | xlWartości, xlFormuły, xlKomentarze |
| Patrzeć na | Opcjonalny | Dopasuj część lub całą komórkę | xlCały, xlCzęść |
| SzukajZamówienie | Opcjonalny | Kolejność wyszukiwania - wiersze lub kolumny | xlByRows, xlByColummns |
| Kierunek wyszukiwania | Opcjonalny | Kierunek wyszukiwania - do przodu lub do tyłu | xlNastępny, xlPoprzedni |
| Sprawa meczu | Opcjonalny | W wyszukiwaniu rozróżniana jest wielkość liter, czy nie | Prawda czy fałsz |
| Dopasuj bajt | Opcjonalny | Używane tylko wtedy, gdy masz zainstalowaną obsługę języków dwubajtowych, np. język chiński | Prawda czy fałsz |
| Format wyszukiwania | Opcjonalny | Zezwól na wyszukiwanie według formatu komórki | Prawda czy fałsz |
Po parametrze i znajdź wiele wartości
Używasz Po parametrze aby określić początkową komórkę wyszukiwania. Jest to przydatne, gdy istnieje więcej niż jedno wystąpienie szukanej wartości.
Jeśli wyszukiwanie znalazło już jedną wartość i wiesz, że zostanie znalezionych więcej wartości, użyj metody Find z parametrem „Po”, aby zarejestrować pierwsze wystąpienie, a następnie użyj tej komórki jako punktu wyjścia do następnego wyszukiwania.
Możesz użyć tego, aby znaleźć wiele wystąpień wyszukiwanego tekstu:
| 123456789101112131415161718192021222324252627282930313233343536 | PodtestMultipleFinds()Dim MyRange jako zakres, OldRange jako zakres, FindStr jako ciąg„Poszukaj pierwszego wystąpienia „Światło i ciepło”Ustaw MyRange = Sheets("Arkusz1").UsedRange.Find("Światło i ciepło")'Jeśli nie znaleziono, wyjdźJeśli MyRange jest niczym, wyjdź z Sub'Wyświetl pierwszy znaleziony adresMsgBox MójZakres.Adres'Utwórz kopię obiektu zakresuUstaw StaryZakres = MójZakres'Dodaj adres do ciągu znaków oddzielonego znakiem „|” postaćZnajdźStr = ZnajdźStr & "|" & Mój.Adres.'Przejdź przez zakres, szukając innych wystąpieńRobić„Wyszukaj „Światło i ciepło”, używając poprzednio znalezionego adresu jako parametru PoUstaw MójZakres = Arkusze("Arkusz1").UsedRange.Find("Światło i ciepło", After:=Range(StaryZakres.Adres))'Jeśli adres został już znaleziony, wyjdź z pętli do - to zatrzymuje ciągłe zapętlenieJeśli InStr(FindStr, MyRange.Address) to zakończ Wykonaj'Wyświetl ostatnio znaleziony adresMsgBox MójZakres.Adres'Dodaj najnowszy adres do ciągu adresówZnajdźStr = ZnajdźStr & "|" & Mój.Adres.'zrób kopię bieżącego zakresuUstaw StaryZakres = MójZakresPętlaNapis końcowy |
Ten kod będzie iterować przez używany zakres i wyświetli adres za każdym razem, gdy znajdzie instancję „Światło i ciepło”
Zauważ, że kod będzie zapętlony, dopóki w FindStr nie zostanie znaleziony zduplikowany adres, w którym to przypadku wyjdzie z pętli Do.
Parametr LookIn
Możesz użyć Parametr LookIn aby określić, w którym składniku komórki chcesz przeszukać. W komórce możesz określić wartości, formuły lub komentarze.
- xlWartości - Wyszukuje wartości komórek (ostateczna wartość komórki po jej obliczeniu)
- xlFormuły - Wyszukiwanie w samej formule komórki (cokolwiek jest wprowadzane do komórki)
- xlKomentarze - Wyszukiwanie w notatkach komórkowych
- xlKomentarzeWątkowe - Wyszukiwania w komentarzach komórek
Zakładając, że formuła została wprowadzona w arkuszu, możesz użyć tego przykładowego kodu, aby znaleźć pierwszą lokalizację dowolnej formuły:
| 12345678910 | Sub TestLookIn()Przyciemnij mój zakres jako zakresUstaw MójZakres = Arkusze("Arkusz1").UsedRange.Find("=", LookIn:=xlFormulas)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
Jeśli parametr „LookIn” został ustawiony na xlValues, kod wyświetli komunikat „Nie znaleziono”. W tym przykładzie zwróci B10.
Korzystanie z parametru LookAt
ten Parametr LookA określa, czy find będzie szukać dokładnego dopasowania komórki, czy też wyszuka dowolną komórkę zawierającą szukaną wartość.
- xlCałość - Wymaga, aby cała komórka pasowała do wartości wyszukiwania
- xlCzęść - Wyszukuje w komórce szukany ciąg
Ten przykład kodu zlokalizuje pierwszą komórkę zawierającą tekst „światło”. Z Lookat:=xlCzęść, zwróci dopasowanie dla „Światło i ciepło”.
| 123456789 | Sub TestLookAt()Przyciemnij mój zakres jako zakresUstaw MyRange = Sheets("Arkusz1").UsedRange.Find("lekki", Lookat:=xlPart)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
Gdyby xlCałość została ustawiona, dopasowanie zostanie zwrócone tylko wtedy, gdy wartość komórki będzie „jasna”.
Parametr kolejności wyszukiwania
ten SearchOrder parametr dyktuje sposób wyszukiwania w całym zakresie.
- xlWiersze - Wyszukiwanie odbywa się wiersz po wierszu
- xlXkolumny - Wyszukiwanie odbywa się kolumna po kolumnie
| 123456789 | Porządek wyszukiwania podrzędnego()Przyciemnij mój zakres jako zakresSet MyRange = Sheets("Arkusz1").UsedRange.Find("pracownik", SearchOrder:=xlColumns)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
Ma to wpływ na to, które dopasowanie zostanie znalezione jako pierwsze.
Korzystając z danych testowych wprowadzonych wcześniej do arkusza roboczego, gdy kolejność wyszukiwania to kolumny, zlokalizowana komórka to A5. Gdy parametr kolejności wyszukiwania zostanie zmieniony na xlRows, zlokalizowana komórka to C4
Jest to ważne, jeśli masz zduplikowane wartości w zakresie wyszukiwania i chcesz znaleźć pierwsze wystąpienie pod określoną nazwą kolumny.
Parametr Kierunku Wyszukiwania
ten Parametr SearchDirection dyktuje, w którym kierunku pójdzie poszukiwanie - skutecznie do przodu lub do tyłu.
- xlDalej - Wyszukaj następną pasującą wartość w zakresie
- xlPoprzedni - Wyszukaj poprzednią pasującą wartość w zakresie
Ponownie, jeśli w zakresie wyszukiwania znajdują się zduplikowane wartości, może to mieć wpływ na to, która z nich zostanie znaleziona jako pierwsza.
| 12345678910 | Podtest KierunekWyszukiwania()Przyciemnij mój zakres jako zakresUstaw MyRange = Sheets("Arkusz1").UsedRange.Find("ciepło", SearchDirection:=xlPrevious)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
Używając tego kodu w danych testowych, kierunek wyszukiwania xlPrevious zwróci lokalizację C9. Użycie parametru xlNext zwróci lokalizację A4.
Parametr Next oznacza, że wyszukiwanie rozpocznie się w lewym górnym rogu zakresu wyszukiwania i będzie działać w dół. Parametr Previous oznacza, że wyszukiwanie rozpocznie się w prawym dolnym rogu zakresu wyszukiwania i będzie działać w górę.
Parametr MatchByte
ten Parametr MatchBye jest używany tylko w przypadku języków, w których każdy znak reprezentuje podwójny bajt, np. chiński, rosyjski i japoński.
Jeśli ten parametr jest ustawiony na „True”, Find dopasuje tylko znaki dwubajtowe ze znakami dwubajtowymi. Jeśli parametr jest ustawiony na „False”, znak dwubajtowy będzie pasował do znaków jedno- lub dwubajtowych.
Parametr formatu wyszukiwania
ten Parametr formatu wyszukiwania umożliwia wyszukiwanie pasujących formatów komórek. Może to być konkretna używana czcionka, czcionka pogrubiona lub kolor tekstu. Przed użyciem tego parametru należy ustawić format wymagany do wyszukiwania przy użyciu właściwości Application.FindFormat.
Oto przykład, jak z niego korzystać:
| 12345678910111213 | PodtestFormatWyszukiwania()Przyciemnij mój zakres jako zakresAplikacja.Znajdź format.WyczyśćApplication.FindFormat.Font.Bold = PrawdaUstaw MyRange = Sheets("Arkusz1").UsedRange.Find("ciepło", Searchformat:=True)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliAplikacja.Znajdź format.WyczyśćNapis końcowy |
W tym przykładzie Znajdź format właściwość jest ustawiona na szukanie pogrubionej czcionki. Instrukcja Find następnie wyszukuje słowo „ciepło”, ustawiając parametr SearchFormat na True, aby zwrócił tylko wystąpienie tego tekstu, jeśli czcionka jest pogrubiona.
W przykładowych danych arkusza roboczego pokazanych wcześniej zwróci to A9, który jest jedyną komórką zawierającą słowo „ciepło” pogrubioną czcionką.
Upewnij się, że właściwość FindFormat jest wyczyszczona na końcu kodu. Jeśli tego nie zrobisz, Twoje następne wyszukiwanie nadal uwzględni to i zwróci nieprawidłowe wyniki.
Jeśli używasz parametru SearchFormat, możesz również użyć symbolu wieloznacznego (*) jako wartości wyszukiwania. W takim przypadku wyszuka dowolną wartość pogrubioną czcionką:
| 1 | Ustaw MyRange = Sheets("Arkusz1").UsedRange.Find("*", Searchformat:=True) |
Korzystanie z wielu parametrów
Wszystkie omawiane tutaj parametry wyszukiwania mogą być używane w połączeniu ze sobą, jeśli jest to wymagane.
Na przykład możesz połączyć parametr „LookIn” z parametrem „MatchCase”, aby spojrzeć na cały tekst komórki, ale uwzględniana jest wielkość liter
| 123456789 | PodtestMultipleParameters()Przyciemnij mój zakres jako zakresUstaw MyRange = Sheets("Arkusz1").UsedRange.Find("Światło i ciepło", LookAt:=xlWhole, MatchCase:=True)Jeśli nie MyRange to nic, toMsgBox MójZakres.AdresW przeciwnym razieMsgBox "Nie znaleziono"Zakończ, jeśliNapis końcowy |
W tym przykładzie kod zwróci A4, ale jeśli użyliśmy tylko części tekstu, np. „ciepło”, nic nie zostanie znalezione, ponieważ dopasowujemy całą wartość komórki. Ponadto nie powiedzie się, ponieważ sprawa nie pasuje.
| 1 | Ustaw MyRange = Sheets("Arkusz1").UsedRange.Find("ciepło", LookAt:=xlWhole, MatchCase:=True) |
Zamień w Excelu VBA
Jak można się spodziewać, istnieje funkcja Zamień w Excel VBA, która działa w bardzo podobny sposób do „Znajdź”, ale zastępuje znalezione wartości w lokalizacji komórki nową wartością.
Są to parametry, których można użyć w instrukcji Replace metody. Działają one dokładnie tak samo, jak w przypadku instrukcji Find method. Jedyna różnica w stosunku do „Znajdź” polega na tym, że musisz określić parametr zastępczy.
| Nazwa | Rodzaj | Opis | Wartości |
| Co | Wymagany | Wartość do wyszukania | Dowolny typ danych, taki jak ciąg lub liczba |
| Wymiana | Wymagany | Ciąg zastępczy. | Dowolny typ danych, taki jak ciąg lub liczba |
| Patrzeć na | Opcjonalny | Dopasuj część lub całą komórkę | xlCzęść lub xlCała |
| SzukajZamówienie | Opcjonalny | Kolejność wyszukiwania - wiersze lub kolumny | xlByRows lub xlByColumns |
| Sprawa meczu | Opcjonalny | W wyszukiwaniu rozróżniana jest wielkość liter, czy nie | Prawda czy fałsz |
| Dopasuj bajt | Opcjonalny | Używane tylko wtedy, gdy masz zainstalowaną obsługę języków dwubajtowych | Prawda czy fałsz |
| Format wyszukiwania | Opcjonalny | Zezwól na wyszukiwanie według formatu komórki | Prawda czy fałsz |
| Zamień format | Opcjonalny | Zamień format dla metody. | Prawda czy fałsz |
Parametr Zamień format wyszukuje komórkę o określonym formacie, np. pogrubienie w ten sam sposób, w jaki parametr SearchFormat działa w metodzie Find. Musisz najpierw ustawić właściwość Application.FindFormat, jak pokazano w przykładowym kodzie wyszukiwania pokazanym wcześniej
Wymień bez parametrów opcjonalnych
Najprościej wystarczy określić, czego szukasz i czym chcesz to zastąpić.
| 123 | PodtestZamień()Arkusze("Arkusz1").UsedRange.Replace What:="Światło i ciepło", Wymiana:="L & H"Napis końcowy |
Należy zauważyć, że metoda Find zwróci tylko pierwsze wystąpienie dopasowanej wartości, podczas gdy metoda Replace działa w całym określonym zakresie i zastępuje wszystko, w czym znajdzie dopasowanie.
Pokazany tutaj kod zastępczy zamieni każde wystąpienie „Light & Heat” na „L & H” w całym zakresie komórek zdefiniowanym przez obiekt UsedRange
Używanie VBA do znajdowania lub zastępowania tekstu w ciągu tekstowym VBA
Powyższe przykłady świetnie sprawdzają się w przypadku korzystania z języka VBA do interakcji z danymi programu Excel. Jednak do interakcji z ciągami VBA można użyć wbudowanych funkcji VBA, takich jak INSTR i REPLACE.
Możesz użyć Funkcja INSTR aby zlokalizować ciąg tekstu w dłuższym ciągu.
| 123 | SubInstr()MsgBox InStr("To jest ciąg MyText", "MyText")Napis końcowy |
Ten przykładowy kod zwróci wartość 9, która jest pozycją liczbową, w której w wyszukiwanym ciągu znajduje się „MyText”.
Pamiętaj, że rozróżniana jest wielkość liter. Jeśli „MyText” ma wszystkie małe litery, zostanie zwrócona wartość 0, co oznacza, że szukany ciąg nie został znaleziony. Poniżej omówimy, jak wyłączyć rozróżnianie wielkości liter.
INSTR - Początek
Dostępne są dwa dodatkowe parametry opcjonalne. Możesz określić punkt początkowy wyszukiwania:
| 1 | MsgBox InStr(9, "To jest ciąg MyText", "MyText") |
Punkt początkowy jest określony jako 9, więc nadal zwróci 9. Jeśli punktem początkowym było 10, zwróci 0 (brak dopasowania), ponieważ punkt początkowy byłby zbyt daleko do przodu.
INSTR – rozróżnianie wielkości liter
Możesz również ustawić parametr Porównaj na vbBinaryCompare lub vbTextPorównaj. Jeśli ustawisz ten parametr, instrukcja musi mieć wartość parametru początkowego.
- vbBinaryCompare - Rozróżniana wielkość liter (domyślnie)
- vbTextPorównaj -Nie uwzględnia wielkości liter
| 1 | MsgBox InStr(1, "To jest ciąg MyText", "mytext", vbTextCompare) |
Ta instrukcja nadal zwróci 9, nawet jeśli wyszukiwany tekst jest pisany małymi literami.
Aby wyłączyć rozróżnianie wielkości liter, możesz również zadeklarować Option Compare Text na górze modułu kodu.
Funkcja zastępowania VBA
Jeśli chcesz zastąpić znaki w ciągu innym tekstem w kodzie, metoda Replace jest do tego idealna:
| 123 | PodtestZamień()MsgBox Replace("To jest ciąg MyText", "MyText", "My Text")Napis końcowy |
Ten kod zastępuje „MyText” na „Mój tekst”. Zauważ, że w wyszukiwanym ciągu rozróżniana jest wielkość liter, ponieważ porównanie binarne jest wartością domyślną.
Możesz również dodać inne parametry opcjonalne:
- Początek - określa pozycję w początkowym ciągu, od której ma zacząć się zamiana. W przeciwieństwie do metody Find zwraca obcięty ciąg, zaczynając od numeru znaku zdefiniowanego przez parametr Start.
- Liczyć - określa liczbę wymian do wykonania. Domyślnie Zamień zmieni każde znalezione wystąpienie wyszukiwanego tekstu, ale możesz ograniczyć to do pojedynczego zastąpienia, ustawiając parametr Count na 1
- Porównywać - podobnie jak w metodzie Find można określić wyszukiwanie binarne lub wyszukiwanie tekstowe za pomocą vbBinaryCompare lub vbTextPorównaj. W pliku binarnym rozróżniana jest wielkość liter, a tekst nie jest rozróżniany
| 1 | MsgBox Replace("To jest ciąg MyText (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Ten kod zwraca „Mój ciąg tekstowy (mytext)”. Dzieje się tak, ponieważ podany punkt początkowy to 9, więc nowy zwracany ciąg zaczyna się od znaku 9. Zmieniono tylko pierwszy „MyText”, ponieważ parametr Count jest ustawiony na 1.
Metoda Replace jest idealna do rozwiązywania problemów, takich jak imiona osób zawierające apostrofy, np. O’Flynn. Jeśli używasz pojedynczych cudzysłowów do zdefiniowania wartości ciągu i występuje apostrof, spowoduje to błąd, ponieważ kod zinterpretuje apostrof jako koniec ciągu i nie rozpozna pozostałej części ciągu.
Możesz użyć metody Replace, aby zastąpić apostrof niczym, usuwając go całkowicie.
