Excel VBA-Ersetzungsfunktion

Wie in Excel haben wir eine Funktion, mit der wir jedes Wort, jedes Zeichen oder jeden Satz finden und durch jeden Buchstaben ersetzen können. Auf diese Weise können wir jedoch immer nur einen Satz oder Buchstaben ersetzen. Mit Hilfe der VBA-Ersetzungsfunktion können wir so viele Wörter, Buchstaben oder Sätze in einer einzigen Aufnahme ersetzen. Dies spart viel Zeit und Mühe, wenn Sie eine Aktivität mehrmals ausführen. Dazu verwenden wir die Replace-Funktion, die in der eingebauten VBA-Funktionsliste enthalten ist.

Unten die Syntax und das Argument der Ersetzungsfunktion in VBA.

Wie verwende ich die Excel VBA-Ersetzungsfunktion?

Wir werden anhand einiger Beispiele lernen, wie man eine VBA-Ersetzungs-Excel-Funktion verwendet.

Sie können diese VBA-Ersetzungs-Excel-Vorlage hier herunterladen - VBA-Ersetzungs-Excel-Vorlage

VBA-Ersetzungsfunktion - Beispiel 1

Wir haben Beispieldaten einiger Sätze, bei denen wir ein Wort ersetzen und den aktualisierten Satz in die Spalte B einfügen.

Führen Sie die folgenden Schritte aus, um die Funktion Ersetzen in Excel VBA zu verwenden.

Schritt 1: Gehen Sie zu VBA und wählen Sie in der Menüregisterkarte Einfügen die Option Modul.

Schritt 2: Schreiben Sie nun eine Unterkategorie in den Namen einer ausgeführten Funktion oder in einen beliebigen Namen nach Ihrer Wahl, wie unten gezeigt.

Code:

 Sub VBA_Replace2 () End Sub

Schritt 3: Definieren Sie nun eine Dimension als Long, da wir einen vollständigen Satz auswählen. Hier haben wir es als X genommen.

Code:

 Sub VBA_Replace2 () Dim X As Long End Sub

Schritt 4: Wählen Sie nun in diesem langen X den maximalen Bereich aus, bis zu dem unsere Daten steigen könnten. Hier haben wir als A1000-Zellbereich genommen und verwenden zum Hochfahren bis zum Beginn unserer Daten End (xlUp), gefolgt von einer Punktzeile (.). Das heißt, wir rücken in der gleichen Spalte bis zu der Zeile vor, in der sich die Daten befinden. Dieser Prozess im Leben Strg + Pfeiltaste nach oben in Excel.

Code:

 Sub VBA_Replace2 () Dim X As Long X = Range ("A1000"). End (xlUp) .Row End Sub

Hinweis : Wir können aus End (xlDown) auswählen, es werden jedoch möglicherweise keine Unterbrechungen oder leere Zellen zwischen den Daten eingefügt, wenn Daten vorhanden sind.

Schritt 5: Definieren Sie nun erneut eine weitere Dimension Y so lange wie unten gezeigt.

Code:

 Sub VBA_Replace2 () Dim X As Long X = Range ("A1000"). End (xlUp) .Row Dim Y As Long End Sub

Schritt 6: Starten Sie nun eine For Next- Schleife für die zweite definierte Dimension Y von der Zellenposition 2 bis zum X-Bereich (letzte gefüllte Zelle).

Hinweis: Wenn Sie Zelle 2 auswählen, wird der Header hier nicht zum Ersetzen in Betracht gezogen.

Code:

 Sub VBA_Replace2 () Dim X As Long X = Range ("A1000"). End (xlUp) .Row Dim Y As Long für Y = 2 To X Next Y End Sub

Schritt 7: Wählen Sie nun den Wert von Spalte B als Bereich als Y, gefolgt von einem Punkt (.). Dies entspricht dem Einfügen einer Funktion in Excel.

Code:

 Sub VBA_Replace2 () Dim X As Long X = Bereich ("A1000"). End (xlUp) .Row Dim Y As Long für Y = 2 To X-Bereich ("B" & Y) .Value = Next Y End Sub

Schritt 8: Wie wir nun in der VBA-Syntax der Ersetzungsfunktion oben gesehen haben, geben Sie Ersetzen ein und wählen Spalte A und Y als ersten Ausdruck der Zeichenfolge, gefolgt von den Wörtern, die wir aus der Tabelle ersetzen müssen.

Code:

 Sub VBA_Replace2 () Dim X As Long X = Bereich ("A1000"). End (xlUp) .Row Dim Y As Long für Y = 2 To X-Bereich ("B" & Y) .Value = Replace (Bereich ("A") "& Y), " Delhi ", " New Delhi ") Next Y End Sub

Schritt 9: Kompilieren Sie nun den vollständigen Code und führen Sie ihn aus. Sobald wir das tun, wird das Wort "Delhi" aus Satz A durch das Werk "New Delhi" ersetzt und der gesamte Satz wird in Satz B kopiert.

Wir können eine Wortfolge oder einen Satz auswählen und diesen durch beliebige Buchstaben oder Sätze ersetzen.

VBA-Ersetzungsfunktion - Beispiel 2

Es gibt eine andere Möglichkeit, Wörter oder Sätze mithilfe der VBA-Codierung zu ersetzen. Dafür haben wir eine Liste von 7 Themen, die wiederholt werden. Und wir müssen diese Betreffnamen durch Hinzufügen von Seriennummern vor jedem Betreffnamen ersetzen, wie unten gezeigt.

Wie wir im obigen Screenshot sehen können, enthält Spalte E eindeutige Namen der Themen, die sich in Spalte B befinden, und Spalte F enthält die Themennamen mit einer Seriennummer am Anfang.

Befolgen Sie die nachstehenden Schritte, um die Funktion "Ersetzen" in VBA zu verwenden.

Schritt 1: Gehen Sie nun zu VBA und fügen Sie im Menü Einfügen ein neues Modul hinzu. Sobald wir es erhalten haben, schreiben Sie die Unterkategorie in den Namen einer Funktion, die wie unten gezeigt ausgeführt wird.

Code:

 Sub VBA_Replace () End Sub

Schritt 2: Betrachten Sie nun eine Dimension Rng als Range, InputRng als Range und ReplaceRng als Range. Sie können beliebige andere Buchstaben oder Wörter zum Definieren von Bereichen auswählen.

Code:

 Sub VBA_Replace () Dimmen als Bereich Dimmen als Bereich, Ersetzen als Bereich Ende Sub

Schritt 3: Verwenden Sie nun xTitleId als Dialogfeld und geben Sie ihm einen Namen. Hier haben wir es " VBA_Replace " genannt.

Code:

 Sub VBA_Replace () Dimmen als Bereich Dimmen als Bereich, Ersetzen als Bereich xTitleId = "VBA_Replace" End Sub

Schritt 4: Weisen Sie nun Application.Selection mit InputRng zu. Dadurch kann die ausgewählte Anwendung in VBA verwendet werden.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection End Sub

Schritt 5: Fügen Sie nun in der nächsten Zeile eine InputBox mit den drei Typen Original range, xTitleId und InputRng ein. Der ursprüngliche Bereich ist eine Liste der Themen, die ersetzt werden müssen, aufgelistet in Spalte B. xTitledId ist der Name des Dialogfelds in der ausgeführten Funktion. Und InputRng ist eine Datentabelle in den Spalten E und F.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Original Range", xTitleId, InputRng.Address, Type: = 8) End Sub

Schritt 6: Ordnen Sie nun in der nächsten Zeile einen Eingang für ReplaceRng zu und wählen Sie dafür die Spalte Replace Range. Rest wie immer.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Original Range", xTitleId, InputRng.Address, Type: = 8) Set ReplaceRng = Application.InputBox ("Ersetzen Bereich:", xTitleId, Typ: = 8) End Sub

Schritt 7: Jetzt verwenden wir die Application.Screenupdating-Funktion, da der Name besagt, dass sie für die Aktualisierung verwendet wird, wenn sie FALSE ist.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Original Range", xTitleId, InputRng.Address, Type: = 8) Set ReplaceRng = Application.InputBox ("Replace Range:", xTitleId, Type: = 8) Application.ScreenUpdating = False End Sub

Schritt 8: Fügen Sie nun eine For-Next-Schleife ein . Ersetzen Sie für jeden Rng-Bereich die Werte aus der ReplaceRng-Spalte.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Original Range", xTitleId, InputRng.Address, Type: = 8) Set ReplaceRng = Application.InputBox ("Ersetzen Range:", xTitleId, Typ: = 8) Application.ScreenUpdating = False Für jedes Rng In ReplaceRng.Columns (1) .Cells Next End Sub

Schritt 9: Ersetzen Sie zuletzt InputRng durch die Werte in Rng aus dem gesamten Blatt.

Code:

 Sub VBA_Replace () Dim Rng als Bereich Dim InputRng als Bereich, ReplaceRng als Bereich xTitleId = "VBA_Replace" Set InputRng = Application.Selection Set InputRng = Application.InputBox ("Original Range", xTitleId, InputRng.Address, Type: = 8) Set ReplaceRng = Application.InputBox ("Replace Range:", xTitleId, Type: = 8) Application.ScreenUpdating = False Für jedes Rng In ReplaceRng.Columns (1) .Cells InputRng.Replace what: = Rng.Value, replacement: = Rng.Offset (0, 1) .Value, Lookat: = xlWhole Next End Sub

Schritt 10: Nachdem Sie fertig sind, kompilieren Sie den Code und führen Sie ihn aus. Wir werden ein Dialogfeld im Namen von VBA_Replace erhalten. Wählen Sie hier die Liste der Themen aus, die als Originalbereich ersetzt werden sollen, und klicken Sie auf OK. Dann erhalten wir ein weiteres Kästchen von dort, wählen Sie den Ersetzungsbereich von E2 bis F8 . Diese Tabelle enthält die Daten, die ersetzt werden müssen, und klicken Sie auf OK.

Wir erhalten Daten in Spalte B, die durch die Daten in Spalte F mit der Seriennummer ersetzt werden.

Zur Übersicht enthält Spalte E eindeutige Betreffnamen. Und Spalte F hat Betreffnamen mit Seriennummern. Also werden durch diese Daten in Spalte B die Daten in Spalte F ersetzt.

Vorteile der VBA-Ersetzungsfunktion

  • Wir können mehrere Wörter oder Sätze in einer einzigen Aufnahme ersetzen.
  • Es gibt keine Begrenzung von Wörtern oder Texten, durch die wir nicht ersetzen können.
  • Die Syntax von Replace in VBA ist so einfach wie die Verwendung der SumIf-Funktion in Excel.
  • VBA-Ersetzung wie in Beispiel 1 gezeigt ist die einfachste Möglichkeit, sie anzuwenden.

Dinge, an die man sich erinnern sollte

  • Speichern Sie als Marco Enabled Excel, um zu vermeiden, dass Sie geschriebenen Code in VBA verlieren.
  • Berücksichtigen Sie die Dimensionen immer so, dass ein Mehrwert entsteht, während wir die Dimensionen im Code auswählen.
  • Stellen Sie sicher, dass Sie den gesamten Bogenbereich auswählen, wie in Beispiel 2 gezeigt, wenn das Ergebnis nur eingeschränkt erhalten werden kann. Oder Sie können einen begrenzten Zellbereich definieren und auswählen, der unter Ersetzen verwendet wird.

Empfohlene Artikel

Dies war eine Anleitung zur VBA-Ersetzungsfunktion. Hier haben wir VBA Replace und die Verwendung der Excel VBA Replace-Funktion zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage besprochen. Sie können auch unsere anderen Artikelvorschläge durchgehen -

  1. Vollständige Tutorials zu VBA-Loops
  2. Wie verwende ich VBA Do While Loop?
  3. Was ist das VBA-Zahlenformat?
  4. Wie erstelle ich eine VBA MsgBox?

Kategorie:

VBA