Ein häufiges Problem ist das Suchen und Ersetzen von Zellwerten. Dafür gibt es im Wesentlichen zwei Herangehensweisen. Den meisten Lesern dürfte die Standard-Funktion “Suchen und Ersetzen”, die entweder über den Eintrag im Register “Start” oder über die Tastenkombination “Strg+H” aufgerufen werden kann, bekannt sein. Die Funktion habe ich auch bereits im Beitrag Sonderzeichen Suchen und Ersetzen in Excel vorgestellt.
Die Alternative ist ein VBA Makro. Eine VBA Lösung ist vor allem dann sinnvoll, wenn Sie regelmäßig den gleichen Zellbereich überprüfen wollen oder gleichzeitig noch weitere Schritte automatisiert durchgeführt werden sollen. In VBA können Sie die Replace-Funktion nutzen. Die Funktion ist wie folgt aufgebaut:
Replace("Ausdruck", "Suchbegriff", "Zielwert")
Optional besteht auch die Möglichkeit die Startposition im Ausdruck, die Anzahl der auszuführenden Ersetzungen von Teilzeichenfolgen und die Art des Vergleichs anzugeben. Auf diese relativ selten zum Einsatz kommenden Optionen gehe ich im heutigen Beitrag nicht ein. Wer sich für diese Optionen interessiert, kann auf der Microsoft Hilfe zur Replace-Funktion weitere Informationen erhalten.
Wie Sie den Zellwert einer einzelnen Zelle überprüfen und den Suchbegriff durch einen Zielwert ersetzen können Sie in folgendem Beispiel für die Zelle A1 nachvollziehen.
Range("A1").Value = Replace(Range("A1").Value, "Suchbegriff", "Zielwert")
In den wenigsten Fällen werden Sie jedoch nur den Zellwert einer einzelnen Zelle überprüfen wollen. Um einen größeren Zellbereich oder eine ganze Spalte überprüfen zu können, benötigen Sie eine Schleife in der Sie Zelle für Zelle einzeln überprüfen und die Zellwerte nacheinander ersetzen. Im nächsten Beispiel wird jede Zelle in Spalte A überprüft und alle gefundenen Suchbegriffe durch die Zielwerte ersetzt.
Range("A:A").Select
For Each rngZelle In Selection
rngZelle.Value = Replace(rngZelle.Value, "Suchbegriff", "Zielwert")
Next rngZelle
Mit diesem VBA Code werden Sie allerdings nicht glücklich werden. Wird ein Zellwert geändert, aktualisiert Excel standardmäßig das Tabellenblatt. Das führt dann bei VBA Codes wie im obigen Beispiel dazu, dass bei jeder Änderung des Zellwertes das Tabellenblatt aktualisiert wird und entsprechend die Laufzeit extrem verlängert. Es gibt nun zwei Möglichkeiten die Laufzeit zu reduzieren. Entweder bauen wir eine IF-Bedingungen ein, so dass nur dann der Zellwert überschrieben wird, wenn tatsächlich eine Änderung erforderlich ist, oder wir deaktiveren die automatische Aktualisierung der Arbeitsmappe während der Verarbeitung.
Die IF-Bedingung hat wiederum den Nachteil, dass bei vielen zu ersetzenden Zellwerten die Laufzeit so immer noch nicht ausreichend reduziert wird. Deutlich effektiver ist daher temporär die automatische Aktualisierung der Arbeitsmappe auszuschalten. Wie das genau funktioniert können Sie im Beitrag Excel Makros beschleunigen nachlesen.
Zellwerte mit VBA suchen und ersetzen
Im abschließenden VBA Code wird also zunächst die automatische Aktualisierung der Arbeitsmappe deaktiviert. Erst dann wird Zelle für Zelle der Suchbegriff gesucht und wenn er gefunden wird ersetzt. Nach Beendigung der Schleife wird dann die automatische Aktualisierung wieder eingeschaltet.
Sub Ersetzen()
Dim lngZeile As Long
Dim rngZelle As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lngZeile = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & lngZeile).Select
For Each rngZelle In Selection
rngZelle.Value = Replace(rngZelle.Value, "Suchbegriff", "Zielwert")
Next rngZelle
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Call Calculate
End Sub
Die Variable lngZeile wurde nur zur besseren Lesbarkeit eingebaut. Natürlich kann der Code auch kompakt auf eine Zeile reduziert werden. Auf die Länge der Laufzeit hat das allerdings keine Auswirkungen. Wichtig ist, dass Sie den VBA Quellcode auch noch zu einem späteren Zeitpunkt noch nachvollziehen können, falls Sie Änderungen vornehmen wollen oder müssen.
Excel VBA suchen und ersetzen im Tabellenblatt
Geht das nicht noch einfacher? Ja, geht es. Allerdings ist die nachfolgende Funktion mit äußerster Vorsicht zu verwenden. Sie können mit VBA auch die bereits erwähnte Standard-Funktion „Suchen und Ersetzen“ ansprechen.
Hier gilt es nur eine Besonderheit zu beachten. Leider ist es nicht möglich festzulegen, ob die das Tabellenblatt oder die komplette Arbeitsmappe durchsucht werden soll. Falls Sie zuletzt in der Arbeitsmappe gesucht haben, wirkt sich das auch auf die VBA Routine aus. Das gilt auch dann, wenn Sie einen bestimmten Zellbereich definieren. Sie ersetzen dann im schlimmsten Fall alle entsprechenden Zeichenketten in der gesamten Arbeitsmappe. Es gibt allerdings einen kleinen Trick um die Suche wieder auf ein Tabellenblatt zurückzusetzen. Bevor Sie die Zeichenkette ersetzen müssen Sie zunächst eine beliebige Suche auf eine beliebige Zeichenkette durchführen. Sie sollten daher immer den nachfolgenden VBA Code zusammen verwenden. Nutzen Sie nur die Zeile 2 kann es ansonsten zu einem Datenverlust führen! Wenn Sie sicher gehen wollen, nutzen Sie lieber den VBA Quellcode aus dem ersten Beispiel.
ActiveSheet.Cells.Find What:="xxxxxxxxxxxxxxxxxxxxxxxx"
Range("A1:A" & Rows.Count).Replace What:="Suchbegriff", Replacement:="Zielwert", LookAt:=xlPart
