Der Beitrag in der letzten Woche beschäftigte sich mit dem Ersetzen von Werten in Excel.
Mit dem heutigen Beitrag zeige ich dir das Ersetzen von Werten mit einem Makro in VBA. Dabei verwende ich die gleichen Beispiele wie im vorangegangenen Beitrag.
Im ersten Beispiel sollte „Düssel“ im Wort „Düsseldorf“ durch „Deggen“ ersetzt werden. „Düsseldorf steht z.B. in Zelle B119.
Das Makro dazu ist dieses:
Sub Ersetzen1()
Cells(121,2).Formula=“=REPLACE(B119,1,6,““Deggen““)“
End Sub
In VBA wird für das Ersetzen die Methode REPLACE verwendet. Das Makro nimmt das Wort aus B119 (Düsseldorf), ersetzt die Zeichenfolge ab dem 1. Zeichen, insgesamt 6 Zeichen, durch das Wort „Deggen“ und trägt den neuen Begriff (Deggendorf) in B121 ein.
In einem zweiten Beispiel soll die Jahreszahl 2016 im abgebildeten Bereich durch 2017 ersetzt werden.
Die Umsetzung realisierst du mit diesem Makro:
Sub Ersetzen2()
Dim rngZelle As Range
For Each rngZelle in Range(„B58:B65“)
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub
Das Makro deklariert die Variable rngZelle. Mit einer For-Next-Schleife werden die Zellinhalte des Bereiches durch „2017“ ersetzt.
Das dritte Beispiel sieht ähnlich aus. Du markierst den Bereich B58:B65, Zellinhalt ist die Jahreszahl 2016. Mit diesem Makro wandelst du die Zahl 2016 in 2017 um:
Sub Ersetzen3()
Dim rngZelle As Range
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,“2016″,“2017″)
Next rngZelle
End Sub
Was ist anders als im zweiten Beispiel? Richtig, du ersetzt nicht die Werte in einem definierten Bereich, sondern in einem selektierten Bereich.
Im vierten Beispiel gibst du die alte und die neue Jahreszahl in separaten Zellen vor.
Und dies ist das Makro dazu:
Sub Ersetzen4()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Range(„E82“)
strNeu=Range(„E83“)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rng>Zelle
End Sub
Beachte bitte, dass auch hier wieder auf den selektierten Bereich abgestellt wurde.
Im fünften Beispiel verwendest du eine Inputbox zum Abfragen der alten und der neuen Jahreszahl.
Sub Erstezen5()
Dim rngZelle As Range
Dim strAlt As String
Dim strNeu As String
strAlt=Inputbox(„altes Jahr“,,“2016″)
strNeu=Inputbox(„neues Jahr“,,“2017″)
For Each rngZelle in Selection
rngZelle.Value=REPLACE(rngZelle.Value,strAlt,strNeu)
Next rngZelle
End Sub
In einem letzten Beispiel geht es darum, die Jahreszahl „2015“ durch „2016“ und die Jahreszahl „2016“ durch „N.N.“ zu ersetzen. Das folgende Bild zeigt links den Zustand vor dem Makro und rechts danach:
Mit diesem Makro setzt du dein Vorhaben um:
Sub Ersetzen6()
Dim rngZelle As Range
For Each rngZelle in Range(„B93:B100“)
If rngZelle.Value=“2016″ Then
rngZelle=REPLACE(rngZelle.Value,“2016″,“N.N.“)
End If
If rngZelle.Value=“2015″ Then
rngZelle=REPLACE(rngZelle.Value,“2015″,“2016″)
End If
Next rngZelle
End Sub
Warum ersetzt du zuerst die Zahl „2016“? Bedenke, wenn du zuerst „2015“ durch „2016“ ersetzt, steht in jeder Zelle des Bereiches „2016“. Diese Zahl ersetzt du dann mit „N.N.“ Das Ergebnis wäre, dass in allen Zellen „N.N.“ steht.
Also ersetzt du zuerst „2016“ durch „N.N.“ und dann „2015“ durch „2016“.
Bei ganzen Spalten oder ganzen Zeilen gehst du analog vor.
Was machst du mit der Funktion ERSETZEN bzw. REPLACE? Lasse es uns in einem Kommentar wissen.
