In diesem Tutorial wird gezeigt, wie Sie die Methoden Suchen und Ersetzen in Excel VBA verwenden.
VBA-Suche
Excel hat ausgezeichnete eingebaute Finden und Ersatz finden Werkzeuge.
Sie können mit den Shortcuts aktiviert werden STRG + F (Suchen) oder STRG + H (Ersetzen) oder über die Multifunktionsleiste: Home > Bearbeiten > Suchen & Auswählen.
Beim Klicken Optionen, können Sie erweiterte Suchoptionen sehen:
Sie können mit VBA einfach auf die Methoden Suchen und Ersetzen zugreifen. Diese integrierten Methoden sind viel schneller als alles, was Sie selbst in VBA schreiben könnten.
VBA-Beispiel finden
Um die Suchfunktionalität zu demonstrieren, haben wir den folgenden Datensatz in Sheet1 erstellt.
Wenn Sie mitmachen möchten, geben Sie die Daten in Ihre eigene Arbeitsmappe ein.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
VBA-Suche ohne optionale Parameter
Wenn Sie die VBA Find-Methode verwenden, können Sie viele optionale Parameter festlegen.
Wir empfehlen dringend, alle Parameter zu definieren, wenn Sie die Find-Methode verwenden!
Wenn Sie die optionalen Parameter nicht definieren, verwendet VBA die aktuell ausgewählten Parameter im Excel-Suchfenster. Dies bedeutet, dass Sie möglicherweise nicht wissen, welche Suchparameter verwendet werden, wenn der Code ausgeführt wird. Find kann für die gesamte Arbeitsmappe oder ein Blatt ausgeführt werden. Es könnte nach Formeln oder Werten suchen. Es gibt keine Möglichkeit, dies zu wissen, es sei denn, Sie überprüfen manuell, was derzeit im Suchfenster von Excel ausgewählt ist.
Der Einfachheit halber beginnen wir mit einem Beispiel ohne definierte optionale Parameter.
Einfaches Suchbeispiel
Schauen wir uns ein einfaches Find-Beispiel an:
| 123456789 | UntertestFind()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("Mitarbeiter")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Dieser Code sucht nach „Mitarbeiter“ im Verwendeten Bereich von Sheet1. Wenn es „Mitarbeiter“ findet, weist es den ersten gefundenen Bereich der Bereichsvariablen MyRange zu.
Als nächstes werden Meldungsfelder mit der Adresse, Spalte und Zeile des gefundenen Textes angezeigt.
In diesem Beispiel werden die standardmäßigen Sucheinstellungen verwendet (vorausgesetzt, sie wurden im Suchfenster von Excel nicht geändert):
- Der Suchtext stimmt teilweise mit dem Zellenwert überein (eine genaue Zellenübereinstimmung ist nicht erforderlich)
- Bei der Suche wird die Groß-/Kleinschreibung nicht beachtet.
- Suchen durchsucht nur ein einzelnes Arbeitsblatt
Diese Einstellungen können mit verschiedenen optionalen Parametern geändert werden (siehe unten).
Methodenhinweise finden
- Suchen wählt nicht die Zelle aus, in der der Text gefunden wird. Es identifiziert nur den gefundenen Bereich, den Sie in Ihrem Code manipulieren können.
- Die Find-Methode findet nur die erste gefundene Instanz.
- Sie können Platzhalter (*) verwenden, z.B. Suche nach „E*“
Nichts gefunden
Existiert der Suchtext nicht, bleibt das Range-Objekt leer. Dies verursacht ein großes Problem, wenn Ihr Code versucht, die Positionswerte anzuzeigen, da sie nicht vorhanden sind. Dies führt zu einer Fehlermeldung, die Sie nicht möchten.
Glücklicherweise können Sie mit dem Is-Operator in VBA auf ein leeres Bereichsobjekt testen:
| 1 | Wenn nicht MyRange nichts ist, dann |
Hinzufügen des Codes zu unserem vorherigen Beispiel:
| 12345678910111213 | UntertestFind()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("Mitarbeiter")Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
Parameter suchen
Bisher haben wir uns nur ein grundlegendes Beispiel für die Verwendung der Find-Methode angesehen. Es stehen jedoch eine Reihe optionaler Parameter zur Verfügung, mit denen Sie Ihre Suche verfeinern können
| Parameter | Typ | Beschreibung | Werte |
| Was | Erforderlich | Der zu suchende Wert | Beliebiger Datentyp wie String oder Numerisch |
| Nach | Optional | Einzelzellverweis, um Ihre Suche zu beginnen | Zellenadresse |
| Nachsehen in | Optional | Verwenden Sie Formeln, Werte, Kommentare für die Suche | xlValues, xlFormulas, xlComments |
| Ansehen | Optional | Einen Teil oder eine ganze Zelle abgleichen | xlGanz, xlTeil |
| SucheBestellung | Optional | Die Reihenfolge, in der gesucht werden soll - Zeilen oder Spalten | xlByRows, xlByColummns |
| SearchDirection | Optional | Richtung für die Suche, um hineinzugehen - vorwärts oder rückwärts | xlNext, xlPrevious |
| MatchCase | Optional | Bei der Suche muss die Groß-/Kleinschreibung beachtet werden oder nicht | Richtig oder falsch |
| MatchByte | Optional | Wird nur verwendet, wenn Sie Doppelbyte-Sprachunterstützung installiert haben, z. chinesische Sprache | Richtig oder falsch |
| Suchformat | Optional | Suche nach Zellenformat zulassen | Richtig oder falsch |
Nach Parameter und Mehrere Werte suchen
Sie verwenden die Nach Parameter um die Startzelle für Ihre Suche anzugeben. Dies ist nützlich, wenn es mehr als eine Instanz des gesuchten Werts gibt.
Wenn bei einer Suche bereits ein Wert gefunden wurde und Sie wissen, dass weitere Werte gefunden werden, verwenden Sie die Find-Methode mit dem Parameter „After“, um die erste Instanz aufzuzeichnen und diese Zelle dann als Ausgangspunkt für die nächste Suche zu verwenden.
Sie können dies verwenden, um mehrere Instanzen Ihres Suchtextes zu finden:
| 123456789101112131415161718192021222324252627282930313233343536 | UntertestMultipleFinds()Dim MyRange As Range, OldRange As Range, FindStr As String'Suchen Sie nach der ersten Instanz von "'Light & Heat"Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat")'Wenn nicht gefunden, dann beendenWenn MyRange nichts ist, dann beenden Sie Sub'Erste gefundene Adresse anzeigenMsgBox MyRange.Address'Kopie des Bereichsobjekts erstellenSet OldRange = MyRange'Adresse zur Zeichenkette hinzufügen, die mit einem "|" begrenzt wird CharakterFindStr = FindStr & "|" & MyRange.Adresse"Durchlaufen Sie den Bereich und suchen Sie nach anderen InstanzenTun'Suchen Sie nach 'Licht & Wärme' mit der zuvor gefundenen Adresse als Nach-ParameterSet MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))'Wenn die Adresse bereits gefunden wurde, dann verlasse die Do-Schleife - dies stoppt die DauerschleifeIf InStr(FindStr, MyRange.Address) Then Exit Do'Zuletzt gefundene Adresse anzeigenMsgBox MyRange.Address'Neueste Adresse zur Adressenfolge hinzufügenFindStr = FindStr & "|" & MyRange.Adresse'machen Sie eine Kopie des aktuellen BereichsSet OldRange = MyRangeSchleifeEnd Sub |
Dieser Code durchläuft den verwendeten Bereich und zeigt die Adresse jedes Mal an, wenn er eine Instanz von "Light & Heat" findet.
Beachten Sie, dass der Code eine Schleife durchläuft, bis eine doppelte Adresse in FindStr gefunden wird. In diesem Fall wird die Do-Schleife verlassen.
LookIn-Parameter
Du kannst den … benutzen LookIn-Parameter um anzugeben, in welcher Komponente der Zelle Sie suchen möchten. Sie können Werte, Formeln oder Kommentare in einer Zelle angeben.
- xlWerte - Durchsucht Zellwerte (der Endwert einer Zelle nach ihrer Berechnung)
- xlFormeln - Sucht innerhalb der Zellformel selbst (was auch immer in die Zelle eingegeben wird)
- xlKommentare - Sucht in Zellnotizen
- xlCommentsThreaded - Sucht in Zellkommentaren
Angenommen, eine Formel wurde in das Arbeitsblatt eingegeben, können Sie diesen Beispielcode verwenden, um die erste Position einer Formel zu finden:
| 12345678910 | UntertestLookIn()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("=", LookIn:=xlFormulas)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
Wenn der Parameter ‚LookIn‘ auf xlValues gesetzt wurde, würde der Code eine ‚Not Found‘-Meldung anzeigen. In diesem Beispiel wird B10 zurückgegeben.
Verwenden des LookAt-Parameters
Die LookAt-Parameter legt fest, ob find nach einer genauen Zellenübereinstimmung oder nach einer Zelle sucht, die den Suchwert enthält.
- xlGanz - Erfordert, dass die gesamte Zelle dem Suchwert entspricht
- xlPart - Sucht innerhalb einer Zelle nach der Suchzeichenfolge
In diesem Codebeispiel wird die erste Zelle gesucht, die den Text „Licht“ enthält. Mit Lookat:=xlPart, es wird ein Match für "Light & Heat" zurückgegeben.
| 123456789 | UntertestLookAt()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
Wenn xlGanz gesetzt wurde, würde eine Übereinstimmung nur zurückgegeben, wenn der Zellenwert „hell“ war.
SearchOrder-Parameter
Die SearchOrder-Parameter bestimmt, wie die Suche im gesamten Bereich durchgeführt wird.
- xlRows - Die Suche erfolgt Zeile für Zeile
- xlXolumns - Die Suche erfolgt Spalte für Spalte
| 123456789 | UntertestSuchreihenfolge()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("Mitarbeiter", SearchOrder:=xlColumns)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
Dies beeinflusst, welche Übereinstimmung zuerst gefunden wird.
Bei Verwendung der zuvor in das Arbeitsblatt eingegebenen Testdaten ist die gefundene Zelle A5, wenn die Suchreihenfolge Spalten ist. Wenn der Parameter der Suchreihenfolge in xlRows geändert wird, ist die gefundene Zelle C4
Dies ist wichtig, wenn Sie im Suchbereich doppelte Werte haben und die erste Instanz unter einem bestimmten Spaltennamen finden möchten.
SearchDirection-Parameter
Die SearchDirection-Parameter bestimmt, in welche Richtung die Suche geht - effektiv vorwärts oder rückwärts.
- xlWeiter - Suche nach dem nächsten passenden Wert im Bereich
- xlZurück - Suche nach dem vorherigen übereinstimmenden Wert im Bereich
Auch hier können doppelte Werte innerhalb des Suchbereichs einen Einfluss darauf haben, welcher zuerst gefunden wird.
| 12345678910 | Unter TestSearchDirection()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("heat", SearchDirection:=xlPrevious)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
Unter Verwendung dieses Codes für die Testdaten gibt eine Suchrichtung von xlPrevious eine Position von C9 zurück. Die Verwendung des Parameters xlNext gibt eine Position von A4 zurück.
Der Parameter Next bedeutet, dass die Suche in der oberen linken Ecke des Suchbereichs beginnt und nach unten arbeitet. Der Parameter Zurück bedeutet, dass die Suche in der unteren rechten Ecke des Suchbereichs beginnt und nach oben arbeitet.
MatchByte-Parameter
Die MatchBye-Parameter wird nur für Sprachen verwendet, die ein Doppelbyte verwenden, um jedes Zeichen darzustellen, wie z. B. Chinesisch, Russisch und Japanisch.
Wenn dieser Parameter auf „True“ gesetzt ist, wird Find nur Doppelbyte-Zeichen mit Doppelbyte-Zeichen abgleichen. Wenn der Parameter auf „False“ gesetzt ist, stimmt ein Doppelbyte-Zeichen mit Einzel- oder Doppelbyte-Zeichen überein.
SearchFormat-Parameter
Die SearchFormat-Parameter ermöglicht Ihnen die Suche nach übereinstimmenden Zellformaten. Dies kann eine bestimmte Schriftart sein, die verwendet wird, eine fette Schriftart oder eine Textfarbe. Bevor Sie diesen Parameter verwenden, müssen Sie das für die Suche erforderliche Format mithilfe der Application.FindFormat-Eigenschaft festlegen.
Hier ist ein Beispiel für die Verwendung:
| 12345678910111213 | UntertestSuchformat()Dimmen Sie MyRange als BereichApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueSet MyRange = Sheets("Sheet1").UsedRange.Find("heat", Searchformat:=True)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennApplication.FindFormat.ClearEnd Sub |
In diesem Beispiel ist die FindFormat -Eigenschaft ist so eingestellt, dass nach einer fetten Schriftart gesucht wird. Die Find-Anweisung sucht dann nach dem Wort „heat“ und setzt den SearchFormat-Parameter auf True, sodass nur eine Instanz dieses Textes zurückgegeben wird, wenn die Schriftart fett ist.
In den zuvor gezeigten Beispielarbeitsblattdaten wird A9 zurückgegeben, was die einzige Zelle ist, die das Wort "Heat" in Fettschrift enthält.
Stellen Sie sicher, dass die FindFormat-Eigenschaft am Ende des Codes deaktiviert ist. Wenn Sie dies nicht tun, wird dies bei Ihrer nächsten Suche dennoch berücksichtigt und es werden falsche Ergebnisse zurückgegeben.
Wenn Sie einen SearchFormat-Parameter verwenden, können Sie auch einen Platzhalter (*) als Suchwert verwenden. In diesem Fall wird nach einem beliebigen Wert mit fetter Schrift gesucht:
| 1 | Set MyRange = Sheets("Sheet1").UsedRange.Find("*", Searchformat:=True) |
Verwenden mehrerer Parameter
Alle hier besprochenen Suchparameter können bei Bedarf in Kombination miteinander verwendet werden.
Sie können beispielsweise den Parameter „LookIn“ mit dem Parameter „MatchCase“ kombinieren, sodass Sie den gesamten Zellentext anzeigen, wobei die Groß-/Kleinschreibung beachtet wird
| 123456789 | Unter TestMultipleParameters()Dimmen Sie MyRange als BereichSet MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True)Wenn nicht MyRange nichts ist, dannMsgBox MyRange.AddressAndersMsgBox "Nicht gefunden"Ende WennEnd Sub |
In diesem Beispiel gibt der Code A4 zurück, aber wenn wir nur einen Teil des Textes verwendet haben, z. ‘heat’, würde nichts gefunden werden, da wir den gesamten Zellenwert abgleichen. Außerdem würde es fehlschlagen, weil die Groß-/Kleinschreibung nicht übereinstimmt.
| 1 | Set MyRange = Sheets("Sheet1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True) |
Ersetzen in Excel VBA
Es gibt, wie zu erwarten, eine Ersetzen-Funktion in Excel VBA, die ähnlich wie "Suchen" funktioniert, aber die Werte an der gefundenen Zellenposition durch einen neuen Wert ersetzt.
Dies sind die Parameter, die Sie in einer Replace-Methodenanweisung verwenden können. Diese funktionieren genauso wie die Methodenanweisung Find. Der einzige Unterschied zu „Suchen“ besteht darin, dass Sie einen Ersetzungsparameter angeben müssen.
| Name | Typ | Beschreibung | Werte |
| Was | Erforderlich | Der zu suchende Wert | Beliebiger Datentyp wie String oder Numerisch |
| Ersatz | Erforderlich | Die Ersatzzeichenfolge. | Beliebiger Datentyp wie String oder Numerisch |
| Ansehen | Optional | Übereinstimmung mit einem Teil oder der gesamten Zelle | xlPart oder xlWhole |
| SucheBestellung | Optional | Die Reihenfolge der Suche in - Zeilen oder Spalten | xlByRows oder xlByColumns |
| MatchCase | Optional | Bei der Suche muss die Groß-/Kleinschreibung beachtet werden oder nicht | Richtig oder falsch |
| MatchByte | Optional | Wird nur verwendet, wenn Sie die Doppelbyte-Sprachunterstützung installiert haben | Richtig oder falsch |
| Suchformat | Optional | Suche nach Zellenformat zulassen | Richtig oder falsch |
| Format ersetzen | Optional | Das Ersetzungsformat für die Methode. | Richtig oder falsch |
Der Parameter Format ersetzen sucht nach einer Zelle mit einem bestimmten Format, z. fett auf die gleiche Weise, wie der Parameter SearchFormat in der Find-Methode funktioniert. Sie müssen zuerst die Application.FindFormat-Eigenschaft festlegen, wie im zuvor gezeigten Find-Beispielcode gezeigt
Ohne optionale Parameter ersetzen
Im einfachsten Fall müssen Sie nur angeben, wonach Sie suchen und durch was Sie es ersetzen möchten.
| 123 | UntertestErsetzen()Sheets("Sheet1").UsedRange.Replace What:="Light & Heat", Replacement:="L & H"End Sub |
Beachten Sie, dass die Find-Methode nur die erste Instanz des übereinstimmenden Werts zurückgibt, während die Replace-Methode den gesamten angegebenen Bereich durchläuft und alles ersetzt, für das sie eine Übereinstimmung findet.
Der hier gezeigte Ersetzungscode ersetzt jede Instanz von ‚Light & Heat‘ durch ‚L & H‘ im gesamten Zellbereich, der durch das UsedRange-Objekt definiert ist
Verwenden von VBA zum Suchen oder Ersetzen von Text in einer VBA-Textzeichenfolge
Die obigen Beispiele funktionieren hervorragend, wenn Sie VBA verwenden, um mit Excel-Daten zu interagieren. Um jedoch mit VBA-Strings zu interagieren, können Sie integrierte VBA-Funktionen wie INSTR und REPLACE verwenden.
Du kannst den … benutzen INSTR-Funktion um eine Textzeichenfolge innerhalb einer längeren Zeichenfolge zu finden.
| 123 | Unter TestInstr()MsgBox InStr("Dies ist MyText-String", "MyText")End Sub |
Dieser Beispielcode gibt den Wert 9 zurück. Dies ist die Nummernposition, an der „MyText“ in der zu suchenden Zeichenfolge gefunden wird.
Beachten Sie, dass die Groß-/Kleinschreibung beachtet wird. Wenn „MyText“ nur in Kleinbuchstaben geschrieben ist, wird ein Wert von 0 zurückgegeben, was bedeutet, dass die Suchzeichenfolge nicht gefunden wurde. Im Folgenden wird erläutert, wie Sie die Groß-/Kleinschreibung deaktivieren.
INSTR - Start
Es stehen zwei weitere optionale Parameter zur Verfügung. Sie können den Startpunkt für die Suche festlegen:
| 1 | MsgBox InStr(9, "Dies ist MyText-String", "MyText") |
Der Startpunkt ist als 9 angegeben, daher wird immer noch 9 zurückgegeben. Wenn der Startpunkt 10 war, würde 0 (keine Übereinstimmung) zurückgegeben, da der Startpunkt zu weit vorne liegen würde.
INSTR - Groß-/Kleinschreibung
Sie können einen Vergleichsparameter auch auf setzen vbBinaryVergleichen oder vbTextVergleichen. Wenn Sie diesen Parameter setzen, muss die Anweisung einen Startparameterwert haben.
- vbBinaryVergleichen - Groß-/Kleinschreibung beachten (Standard)
- vbTextVergleichen - Nicht Groß-/Kleinschreibung beachten
| 1 | MsgBox InStr(1, "Dies ist MyText-String", "mytext", vbTextCompare) |
Diese Anweisung gibt immer noch 9 zurück, obwohl der Suchtext in Kleinbuchstaben geschrieben ist.
Um die Groß-/Kleinschreibung zu deaktivieren, können Sie auch die Option Compare Text oben in Ihrem Codemodul deklarieren.
VBA-Ersetzungsfunktion
Wenn Sie innerhalb Ihres Codes Zeichen in einer Zeichenfolge durch einen anderen Text ersetzen möchten, ist die Methode Replace dafür ideal:
| 123 | UntertestErsetzen()MsgBox Replace("Dies ist MyText-String", "MyText", "My Text")End Sub |
Dieser Code ersetzt „MyText“ durch „My Text“. Beachten Sie, dass bei der Suchzeichenfolge die Groß-/Kleinschreibung beachtet wird, da ein binärer Vergleich der Standard ist.
Sie können auch andere optionale Parameter hinzufügen:
- Start - definiert die Position im Anfangsstring, von der aus die Ersetzung beginnen soll. Im Gegensatz zur Find-Methode gibt sie eine abgeschnittene Zeichenfolge ab der durch den Start-Parameter definierten Zeichennummer zurück.
- Zählen - definiert die Anzahl der durchzuführenden Ersetzungen. Standardmäßig ändert Replace jede Instanz des gefundenen Suchtextes, aber Sie können dies auf eine einzelne Ersetzung beschränken, indem Sie den Count-Parameter auf 1 . setzen
- Vergleichen - wie bei der Find-Methode können Sie mit . eine binäre Suche oder eine Textsuche angeben vbBinaryVergleichen oder vbTextVergleichen. Bei Binär wird die Groß-/Kleinschreibung beachtet und bei Text wird die Groß-/Kleinschreibung nicht beachtet
| 1 | MsgBox Replace("Dies ist MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Dieser Code gibt "My Text string (mytext)" zurück. Dies liegt daran, dass der angegebene Startpunkt 9 ist, sodass die neue zurückgegebene Zeichenfolge bei Zeichen 9 beginnt. Nur der erste ‚MyText‘ wurde geändert, da der Parameter Count auf 1 gesetzt ist.
Die Methode Ersetzen ist ideal zum Lösen von Problemen wie Personennamen, die Apostrophe enthalten, z. O’Flynn. Wenn Sie einfache Anführungszeichen verwenden, um einen Zeichenfolgenwert zu definieren, und ein Apostroph vorhanden ist, führt dies zu einem Fehler, da der Code das Apostroph als Ende der Zeichenfolge interpretiert und den Rest der Zeichenfolge nicht erkennt.
Sie können die Methode Replace verwenden, um das Apostroph durch nichts zu ersetzen und es vollständig zu entfernen.
