Suchen und Ersetzen in Excel-VBA verwenden

In diesem Tutorial zeigen wir Ihnen, wie Sie die Methoden Suchen und Ersetzen in Excel-VBA verwenden können.

VBA – Suchen

Excel verfügt über ausgezeichnete eingebaute Werkzeuge zum Suchen und Ersetzen.

Sie können mit den Tastenkombinationen STRG + F (Suchen) bzw.  STRG + H (Ersetzen) oder über die Menüleiste aktiviert werden: Start > Bearbeiten > Suchen und Auswählen.

Wenn Sie auf Optionen klicken, können Sie erweiterte Suchoptionen sehen:

Mit VBA können Sie leicht auf diese Methoden zugreifen.

VBA-Beispiel für die Suche

Um die Suchfunktion zu demonstrieren, haben wir den folgenden Datensatz in Tabelle1 erstellt.

Wenn Sie das Beispiel nachvollziehen möchten, geben Sie die Daten in Ihre eigene Arbeitsmappe ein.

VBA-Suchen ohne optionale Parameter

Bei der Verwendung der VBA-Suchmethode gibt es viele optionale Parameter, die Sie einstellen können.

Wir empfehlen dringend, alle Parameter zu definieren, wenn Sie die Find-Methode verwenden!

Wenn Sie die optionalen Parameter nicht definieren, verwendet VBA die aktuell im Excel-Suchfenster ausgewählten Parameter. Das bedeutet, dass Sie möglicherweise nicht wissen, welche Suchparameter verwendet werden, wenn der Code ausgeführt wird. Die Suche kann für die gesamte Arbeitsmappe oder ein Blatt ausgeführt werden. Es könnte nach Formeln oder Werten gesucht werden. Es gibt keine Möglichkeit, dies herauszufinden, es sei denn, Sie überprüfen manuell, was derzeit im Excel-Suchfenster ausgewählt ist.

Der Einfachheit halber beginnen wir mit einem Beispiel, bei dem keine optionalen Parameter definiert sind.

Einfaches Suchbeispiel

Schauen wir uns ein einfaches Suchbeispiel an:

Sub Suchen_Test()
Dim MeinBereich As Range

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Personal")
MsgBox MeinBereich.Address
MsgBox MeinBereich.Column
MsgBox MeinBereich.Row

End Sub

In diesem Code wird im verwendeten Bereich von Tabelle1 nach „Personal“ gesucht. Wenn er „Personal“ findet, wird der erste gefundene Bereich der Bereichsvariablen MeinBereich zugewiesen.

Anschließend werden Meldungsfelder mit der Adresse, der Spalte und der Zeile des gefundenen Textes angezeigt.

In diesem Beispiel werden die Standard-Sucheinstellungen verwendet (vorausgesetzt, sie wurden im Excel-Suchfenster nicht geändert):

• Der Suchtext wird teilweise mit dem Zellenwert abgeglichen (eine exakte Zellenübereinstimmung ist nicht erforderlich)
• Bei der Suche wird nicht zwischen Groß- und Kleinschreibung unterschieden.
• Die Suchabfrage durchsucht nur ein einziges Arbeitsblatt

Diese Einstellungen können mit verschiedenen optionalen Parametern geändert werden (siehe unten).

Hinweise zur Find-Methode

• Die Suche wählt nicht die Zelle aus, in der der Text gefunden wurde. Sie identifiziert nur den gefundenen Bereich, den Sie in Ihrem Code bearbeiten können.
• Die Find-Methode findet nur die erste gefundene Instanz.
• Sie können Platzhalter (*) verwenden (z. B. die Suche nach ‚E*‘)

Nichts gefunden

Wenn der Suchtext nicht vorhanden ist, bleibt das Bereichsobjekt leer. Dies führt zu einem großen Problem, wenn Ihr Code versucht, die Koordinatenwerte anzuzeigen, weil sie nicht vorhanden sind. Dies führt zu einer Fehlermeldung, die Sie nicht wünschen.

Glücklicherweise können Sie in VBA mit dem Is-Operator testen, ob ein Bereichsobjekt leer ist:

If Not MeinBereich Is Nothing Then

Fügen Sie den Code zu unserem vorherigen Beispiel hinzu:

Sub Suchen_Test()
Dim MeinBereich As Range

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Personal")
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
    MsgBox MeinBereich.Column
    MsgBox MeinBereich.Row
Else
    MsgBox "Nicht gefunden"
End If
End Sub 

Parameter der Suchmethode

Bisher haben wir uns nur ein einfaches Beispiel für die Verwendung der Find-Methode angesehen. Es gibt jedoch eine Reihe von optionalen Parametern, mit denen Sie Ihre Suche verfeinern können:

Parameter	Typ	Beschreibung	Werte
What	Erforderlich	Der Wert, nach dem gesucht werden soll	Beliebiger Datentyp, z. B. eine Zeichenkette oder ein numerischer Wert
After	Optional	Einzelner Zellenbezug, um Ihre Suche zu beginnen	Adresse der Zelle
LookIn	Optional	Formeln, Werte, Kommentare für die Suche verwenden	xlValues, xlFormulas, xlComments
LookAt	Optional	Einen Teil oder die gesamte Zelle abgleichen	xlWhole, xlPart
SearchOrder	Optional	Die Reihenfolge, in der gesucht werden soll  (Zeilen oder Spalten)	xlByRows, xlByColummns
SearchDirection	Optional	Die Richtung, in der gesucht werden soll (vorwärts oder rückwärts)	xlNext, xlPrevious
MatchCase	Optional	Groß- und Kleinschreibung bei der Suche beachten oder nicht	True oder False
MatchByte	Optional	Wird nur verwendet, wenn Sie Doppelbyte-Sprachunterstützung installiert haben (z. B. Chinesisch)	True oder False
SearchFormat	Optional	Erlaubt die Suche nach dem Zellenformat	True oder False

Der After-Parameter und die Suche nach mehreren Werten

Mit dem After-Parameter geben Sie die Anfangszelle für Ihre Suche an. Dies ist nützlich, wenn es mehr als eine Instanz vom gesuchten Wert gibt.

Wenn eine Suchanfrage bereits einen Wert gefunden hat und Sie wissen, dass weitere Werte gefunden werden, dann verwenden Sie die Find-Methode mit dem Parameter „After“, um die erste Instanz zu erfassen und ihre Zelle als Ausgangspunkt für die nächste Suche zu verwenden.

Auf diese Weise können Sie mehrere Instanzen Ihres Suchtextes finden:

Sub MehrfacheSuche_Test()
Dim MeinBereich As Range, AlterBereich As Range, SuchenStr As String

'Suche nach dem ersten Beispiel von "Licht & Wärme"
Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Licht & Wärme")

'Wenn nicht gefunden, dann beenden
If MeinBereich Is Nothing Then Exit Sub

'Erste gefundene Adresse anzeigen
MsgBox MeinBereich.Address

'Eine Kopie des Bereichsobjekts erstellen
Set AlterBereich = MeinBereich

'Einfügen der Adresse in die Zeichenkette, die mit einem "|"-Zeichen begrenzt wird
SuchenStr = SuchenStr & "|" & MeinBereich.Address

'Durch den Bereich iterieren und nach weiteren Instanzen suchen
Do
    'Nach 'Licht & Wärme' unter Verwendung der zuvor gefundenen Adresse als After-Parameter suchen   
    Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Licht & Wärme", After:=Range(AlterBereich.Address))

    'Wenn die Adresse bereits gefunden wurde, wird die DO-Schleife verlassen. Dadurch wird die Endlosschleife beendet
    If InStr(SuchenStr, MeinBereich.Address) Then Exit Do
    
    'Letzte gefundene Adresse anzeigen
    MsgBox MeinBereich.Address

    'Die letzte Adresse zur Adressenkette hinzufügen
    SuchenStr = SuchenStr & "|" & MeinBereich.Address

    'Eine Kopie des aktuellen Bereichs erstellen
     Set AlterBereich = MeinBereich
Loop
End Sub

Dieser Code durchläuft den verwendeten Bereich und zeigt die Adresse jedes Mal an, wenn er eine Instanz von „Licht & Wärme“ findet.

Beachten Sie, dass der Code eine Schleife durchlaufen lässt, bis eine doppelte Adresse in SuchenStr gefunden wird. In diesem Fall wird die Do-Schleife verlassen.

Der LookIn-Parameter

Mit dem LookIn-Parameter können Sie angeben, in welcher Zellenkomponente Sie suchen möchten. Sie können Werte, Formeln oder Kommentare in einer Zelle angeben.

• xlValues – Sucht nach Zellenwerten (der Endwert einer Zelle nach ihrer Berechnung)
• xlFormulas – Sucht in der Zellenformel selbst (was auch immer in die Zelle eingegeben wurde)
• xlComments – Sucht in den Zellennotizen
• xlCommentsThreaded – Sucht in den Zellenkommentaren

Unter der Annahme, dass eine Formel in das Arbeitsblatt eingegeben wurde, können Sie diesen Beispielcode verwenden, um die erste Stelle einer beliebigen Formel zu finden:

Sub Test_LookIn()
Dim MeinBereich As Range

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("=", LookIn:=xlFormulas)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address   
Else
    MsgBox "Nicht gefunden"
 End If
End Sub

Wenn der LookIn-Parameter auf xlValues gesetzt wäre, würde der Code die Meldung „Nicht gefunden“ anzeigen. In diesem Beispiel wird B10 zurückgegeben.

Verwendung des LookAt-Parameters

Der LookAt-Parameter bestimmt, ob Find nach einer exakten Zellenübereinstimmung oder nach einer  beliebigen Zelle, die den Suchwert enthält, sucht.

• xlWhole – Erfordert die Übereinstimmung der gesamten Zelle mit dem Suchwert
• xlPart – Sucht innerhalb einer Zelle nach dem Suchbegriff

In diesem Codebeispiel wird die erste Zelle, die den Text „Licht“ enthält, gesucht. Mit Lookat:=xlPart wird eine Übereinstimmung in „Licht & Wärme“ gefunden.

Sub Test_LookAt()
Dim MeinBereich As Range
Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Licht", Lookat:=xlPart)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
  Else
    MsgBox "Nicht gefunden"  
End If
End Sub

Wäre xlWhole eingestellt, würde nur dann eine Übereinstimmung erzielt, wenn der Zellenwert „Licht“ wäre.

Der SearchOrder-Parameter

Der SearchOrder-Parameter gibt vor, wie die Suche im gesamten Bereich durchgeführt wird.

• xlRows – Die Suche wird zeilenweise durchgeführt
• xlColumns – Die Suche erfolgt spaltenweise

Sub Test_SearchOrder()
Dim MeinBereich As Range
Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Personal", SearchOrder:=xlColumns)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
Else
    MsgBox "Nicht gefunden"
End If
End Sub

Dies beeinflusst, welche Übereinstimmung zuerst gefunden wird.

Bei Verwendung der zuvor in das Arbeitsblatt eingegebenen Testdaten ist die gesuchte Zelle A5, wenn die SearchOrder xlColumns entspricht. Wenn der Parameter für SearchOrder in xlRows geändert wird, wird Zelle C4 gefunden.

Dies ist wichtig, wenn es innerhalb des Suchbereichs doppelte Werte gibt und Sie das erste Vorkommen unter einem bestimmten Spaltennamen finden möchten.

Der SearchDirection-Parameter

Der SearchDirection-Parameter gibt an, in welche Richtung die Suche gehen soll (vorwärts oder rückwärts).

• xlNext – Suche nach dem nächsten übereinstimmenden Wert im Bereich
• xlPrevious – Suche nach dem vorherigen übereinstimmenden Wert im Bereich

Auch hier gilt: Wenn es innerhalb des Suchbereichs doppelte Werte gibt, kann dies Auswirkungen darauf haben, welcher Wert zuerst gefunden wird.

Sub Test_SearchDirection()
Dim MeinBereich As Range

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Wärme", SearchDirection:=xlPrevious)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
Else
    MsgBox "Nicht gefunden"
End If
End Sub

Bei Verwendung dieses Codes für die Testdaten ergibt eine Suchrichtung von xlPrevious die Position C9. Die Verwendung des Parameters xlNext führt zu A4.

Der Parameter Next bedeutet, dass die Suche in der linken oberen Ecke des Suchbereichs beginnt und sich nach unten bewegt. Der Parameter Previous bedeutet, dass die Suche in der rechten unteren Ecke des Suchbereichs beginnt und nach oben führt.

Der MatchByte-Parameter

Der MatchBye-Parameter wird nur für Sprachen, die ein Doppelbyte zur Darstellung jedes Zeichens verwenden wie z. B. Chinesisch, Russisch und Japanisch, verwendet.

Wenn dieser Parameter auf ‚True‘ gesetzt ist, wird Find nur Doppelbyte-Zeichen mit Doppelbyte-Zeichen abgleichen. Wenn der Parameter auf „False“ gesetzt ist, wird ein Doppelbyte-Zeichen mit Einzel- oder Doppelbyte-Zeichen abgeglichen.

Der SearchFormat-Parameter

Mit dem SearchFormat–Parameter können Sie nach passenden Zellenformaten suchen. Dabei kann es sich um eine bestimmte Schriftart, eine fette Schrift oder eine Textfarbe handeln. Bevor Sie diesen Parameter verwenden, müssen Sie das für die Suche erforderliche Format mit der Eigenschaft Application.FindFormat festlegen.

Hier ist ein Beispiel für die Verwendung dieses Parameters:

Sub Test_SearchFormat()
Dim MeinBereich As Range

Application.FindFormat.Clear
Application.FindFormat.Font.Bold = True
Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Wärme", Searchformat:=True)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
Else
    MsgBox "Nicht gefunden"
End If
Application.FindFormat.Clear
End Sub

In diesem Beispiel wird die Eigenschaft FindFormat so eingestellt, dass nach einer fetten Schriftart gesucht wird. Die Find-Anweisung sucht dann nach dem Wort „Wärme“ und setzt den Parameter „SearchFormat“ auf „True“, so dass eine Instanz dieses Textes nur zurückgegeben wird, wenn die Schriftart fett ist.

In dem zuvor gezeigten Beispiel-Arbeitsblatt gibt dies die Zelle A9 zurück, die als einzige das Wort „Wärme“ in einer fetten Schriftart enthält.

Stellen Sie sicher, dass die Eigenschaft FindFormat am Ende des Codes gelöscht wird. Wenn Sie dies nicht tun, wird Ihre nächste Suche dies immer noch berücksichtigen und falsche Ergebnisse liefern.

Wo Sie einen SearchFormat-Parameter verwenden, können Sie auch einen Platzhalter (*) als Suchwert verwenden. In diesem Fall wird nach jedem Wert mit fetter Schrift gesucht:

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("*", Searchformat:=True)

Mehrere Parameter verwenden

Alle hier besprochenen Suchparameter können bei Bedarf auch in Kombination miteinander verwendet werden.

So können Sie beispielsweise den Parameter „LookIn“ mit dem Parameter „MatchCase“ kombinieren, um den gesamten Zellentext zu durchsuchen, wobei die Groß- und Kleinschreibung jedoch beachtet wird.

Sub MehrereParameter_Test()
Dim MeinBereich As Range
Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Licht & Wärme", LookAt:=xlWhole, MatchCase:=True)
If Not MeinBereich Is Nothing Then
    MsgBox MeinBereich.Address
Else
    MsgBox "Nicht gefunden"
End If
End Sub

In diesem Beispiel gibt der Code A4 zurück, aber wenn wir nur einen Teil des Textes wie z. B. „Wärme“ verwendet hätten, würde nichts gefunden werden, da wir den gesamten Zellenwert abgleichen. Außerdem würde die Suche fehlschlagen, weil die Groß- und Kleinschreibung nicht übereinstimmt.

Set MeinBereich = Sheets("Tabelle1").UsedRange.Find("Wärme", LookAt:=xlWhole, MatchCase:=True)

Ersetzen in Excel-VBA

Wie zu erwarten, gibt es in Excel-VBA eine Ersetzen-Funktion, die ähnlich wie die Suchfunktion funktioniert, aber die Werte an der gefundenen Zellenposition durch einen neuen Wert ersetzt.

Dies sind die Parameter, die Sie in einer Anweisung der Replace-Methode verwenden können. Sie funktionieren genauso wie bei der Anweisung der Find-Methode. Der einzige Unterschied zu „Find“ besteht darin, dass Sie einen Replacement-Parameter angeben müssen.

Name	Typ	Beschreibung	Werte
What	Erforderlich	Der Wert, nach dem gesucht werden soll	Beliebiger Datentyp, z. B. eine Zeichenkette oder ein numerischer Wert
Replacement	Erforderlich	Die Ersatzzeichenfolge.	Beliebiger Datentyp, z. B. eine Zeichenkette oder ein numerischer Wert
LookAt	Optional	Einen Teil oder die gesamte Zelle abgleichen	xlPart oder xlWhole
SearchOrder	Optional	Die Reihenfolge, in der gesucht werden soll (Zeilen oder Spalten)	xlByRows oder xlByColumns
MatchCase	Optional	Die Groß-/Kleinschreibung bei der Suche berücksichtigen oder nicht	True oder False
MatchByte	Optional	Wird nur verwendet, wenn Sie Doppelbyte-Sprachunterstützung installiert haben	True oder False
SearchFormat	Optional	Erlaubt die Suche nach dem Zellenformat	True oder False
ReplaceFormat	Optional	Das Ersatzformat für die Methode.	True oder False

Der Parameter ReplaceFormat sucht nach einer Zelle mit einem bestimmten Format wie z. B. fett auf dieselbe Weise wie der Parameter SearchFormat in der Find-Methode. Sie müssen zunächst die Eigenschaft Application.FindFormat wie im zuvor gezeigten Find-Beispielcode gezeigt festlegen.

Ersetzen ohne optionale Parameter

Im einfachsten Fall müssen Sie nur angeben, wonach Sie suchen und wodurch Sie es ersetzen wollen.

Sub Ersetzen_Test()
Sheets("Tabelle1").UsedRange.Replace What:="Licht & Wärme", Replacement:="L & W"
End Sub

Beachten Sie, dass die Find-Methode nur die erste Instanz des übereinstimmenden Wertes zurückgibt, während die Replace-Methode den gesamten angegebenen Bereich abarbeitet und alle Treffer, die sie findet, ersetzt.

Der hier gezeigte Ersetzungscode ersetzt jede Instanz von „Licht & Wärme“ durch „L & H“ im gesamten durch das UsedRange-Objekt definierten Zellenbereich.

Verwenden von VBA zum Suchen oder Ersetzen von Text innerhalb einer VBA-Textzeichenfolge

Die obigen Beispiele eignen sich hervorragend für die Verwendung von VBA zur Interaktion mit Excel-Daten. Für die Interaktion mit VBA-Zeichenfolgen können Sie jedoch auch integrierte VBA-Funktionen wie INSTR und REPLACE verwenden.

Sie können die INSTR-Funktion verwenden, um eine Textzeichenfolge innerhalb einer längeren Zeichenfolge zu finden.

Sub Test_Instr()
MsgBox InStr("Dies ist die MeinText-Zeichenkette", "MeinText")
End Sub

Dieser Beispielcode gibt den Wert 14 zurück, d. h. die Position, an der „MeinText“ in der zu durchsuchenden Zeichenfolge gefunden wird.

Beachten Sie, dass die Groß- und Kleinschreibung eine Rolle spielt. Wenn „MeinText“ in Kleinbuchstaben geschrieben ist, wird der Wert 0 zurückgegeben, was bedeutet, dass die zu suchende Zeichenfolge nicht gefunden wurde. Nachfolgend wird beschrieben, wie Sie die Groß- und Kleinschreibung abschalten können.

INSTR – Anfang

Es sind zwei weitere optionale Parameter verfügbar. Sie können den Anfangspunkt für die Suche angeben:

MsgBox InStr(14, "Dies ist die MeinText-Zeichenkette", "MeinText")

Der Anfangspunkt ist mit 14 angegeben, so dass die Suche trotzdem 14 ergibt. Wäre der Startpunkt 15, würde die Suche 0 (keine Übereinstimmung) ergeben, da der Startpunkt zu weit vorne liegen würde.

INSTR – Unterscheidung zwischen Groß- und Kleinschreibung

Sie können auch einen Vergleichsparameter auf vbBinaryCompare oder vbTextCompare setzen. Wenn Sie diesen Parameter einstellen, muss die Anweisung einen Anfangsparameterwert haben.

• vbBinaryCompare – Groß-/Kleinschreibung beachten (Standard)
• vbTextCompare – Groß-/Kleinschreibung nicht beachten

MsgBox InStr(1, "Dies ist die MeinText-Zeichenkette", "meinText", vbTextCompare)

Diese Anweisung gibt immer noch 14 zurück, auch wenn der Suchtext in Kleinbuchstaben geschrieben ist.

Um die Unterscheidung zwischen Groß- und Kleinschreibung zu deaktivieren, können Sie auch Option Compare Text am Anfang Ihres Codemoduls deklarieren.

VBA-Ersetzungsfunktion

Wenn Sie in Ihrem Code Zeichen in einer Zeichenkette durch einen anderen Text ersetzen möchten, ist die Replace-Methode ideal dafür geeignet:

Sub Replace_Test()
MsgBox Replace("Dies ist die MeinText-Zeichenkette", "MeinText", "Mein Text")
End Sub

In diesem Code wird „MeinText“ durch „Mein Text“ ersetzt. Beachten Sie, dass die Groß- und Kleinschreibung bei der zu suchenden Zeichenfolge zu beachten ist, da standardmäßig ein binärer Vergleich durchgeführt wird.

Sie können auch andere optionale Parameter hinzufügen:

• Start – definiert die Position in der ursprünglichen Zeichenfolge, an der die Ersetzung beginnen soll. Anders als bei der Find-Methode wird hier eine abgeschnittene Zeichenkette, die mit der durch den Start-Parameter definierten Zeichennummer beginnt, zurückgegeben.
• Count – legt die Anzahl der Ersetzungen fest, die vorgenommen werden sollen. Standardmäßig wird Replace jede gefundene Instanz des Suchtextes ändern, aber Sie können dies auf eine einzelne Ersetzung beschränken, indem Sie den Count-Parameter auf 1 setzen
• Compare – wie bei der Find-Methode können Sie eine binäre Suche oder eine Textsuche mit vbBinaryCompare oder vbTextCompare angeben. Bei der binären Suche wird zwischen Groß- und Kleinschreibung unterschieden und bei der Textsuche nicht

MsgBox Replace("Dies ist die MeinText-Zeichenkette (meinText)", "MeinText", "Mein Text", 14, 1, vbTextCompare)

Dieser Code gibt „MeinText-Zeichenkette (meinText)“ zurück. Dies liegt daran, dass der angegebene Anfangspunkt 14 ist, so dass die neue zurückgegebene Zeichenfolge bei Zeichen 14 beginnt. Nur der erste „MeinText“ wurde geändert, da der Parameter Count auf 1 gesetzt ist.

Die Replace-Methode ist ideal für die Lösung von Problemen wie Namen von Personen, die Apostrophe enthalten wie z. B. O’Flynn. Wenn Sie einfache Anführungszeichen verwenden, um einen Zeichenfolgenwert zu definieren und ein Hochkomma enthalten ist, führt dies zu einem Fehler, da der Code das Hochkomma als das Ende der Zeichenfolge interpretiert und den Rest der Zeichenfolge nicht erkennt.

Sie können die Replace-Methode verwenden, um das Hochkomma durch nichts zu ersetzen und es somit vollständig zu entfernen.