Excel VBA-vervangfunctie

Net als in Excel hebben we een functie waarmee we elk woord, teken of zin kunnen vinden en vervangen door elke letter. Maar door dat proces kunnen we slechts één soort zin of letter tegelijk vervangen. Met behulp van de VBA-vervangfunctie kunnen we zoveel woorden, letters of zinnen in één opname vervangen. Dit bespaart enorme tijd en moeite bij het meerdere keren uitvoeren van één activiteit. Hiervoor gebruiken we de functie Vervangen in de ingebouwde VBA-functielijst.

Hieronder de syntaxis en het argument van de functie Vervangen in VBA.

Hoe de Excel VBA-vervangfunctie te gebruiken?

We zullen leren hoe een VBA Excel-functie te gebruiken met enkele voorbeelden.

U kunt deze VBA Excel-sjabloon hier downloaden - VBA Excel-sjabloon vervangen

VBA-vervangfunctie - Voorbeeld # 1

We hebben voorbeeldgegevens van enkele zinnen waarbij we één woord vervangen en die bijgewerkte zin in kolom Kolom B plakken.

Volg de onderstaande stappen om de functie Vervangen in Excel VBA te gebruiken.

Stap 1: Ga naar VBA en selecteer Module in het menutabblad Invoegen.

Stap 2: Schrijf nu een subcategorie in de naam van een uitgevoerde functie of in een willekeurige naam volgens uw keuze, zoals hieronder weergegeven.

Code:

 Sub VBA_Replace2 () End Sub

Stap 3: definieer nu een dimensie als Lang, aangezien we een volledige zin zullen selecteren. Hier hebben we het als X genomen.

Code:

 Sub VBA_Replace2 () Dim X As Long End Sub

Stap 4: Selecteer nu in die Lange X het maximale bereik tot waar onze gegevens omhoog kunnen gaan. Hier hebben we het celbereik van A1000 genomen en om omhoog te gaan tot onze gegevens beginnen, gebruikt u End (xlUp) gevolgd door een punt (.) Rij. Wat betekent dat we omhoog gaan in dezelfde kolom tot die rij met de gegevens. Dit proces in het leven Ctrl + pijl-omhoog in Excel.

Code:

 Sub VBA_Replace2 () Dim X zo lang X = bereik ("A1000"). End (xlUp) .Row End Sub

Opmerking : we kunnen kiezen uit End (xlDown), maar het mag geen pauze of lege cel tussen de gegevens toevoegen als de gegevens dat hebben.

Stap 5: Definieer nu opnieuw een dimensie Y zolang als hieronder wordt getoond.

Code:

 Sub VBA_Replace2 () Dim X zo lang X = bereik ("A1000"). Einde (xlUp). Rij dim Y als lang einde Sub

Stap 6: Start nu een For Next- lus voor de tweede gedefinieerde dimensie Y van celpositie 2 tot en met X-bereik (laatst gevulde cel)

Opmerking: Als u cel 2 selecteert, houden we hier geen rekening met de koptekst voor vervanging.

Code:

 Sub VBA_Replace2 () Dim X zo lang X = bereik ("A1000"). Einde (xlUp). Rij dim Y zo lang voor Y = 2 tot X Volgende Y End Sub

Stap 7: Selecteer nu de waarde van kolom B als bereik als Y gevolgd door een punt (.) Dit is hetzelfde als het invoegen van een functie in Excel.

Code:

 Sub VBA_Replace2 () Dim X zo lang X = bereik ("A1000"). Einde (xlUp). Rij dim Y zo lang voor Y = 2 tot X bereik ("B" en Y). Waarde = Volgende Y Einde Sub

Stap 8: Typ nu, zoals we hebben gezien in de VBA-syntaxis van de functie Vervangen hierboven, Vervang en selecteer Kolom A en Y als de eerste uitdrukking van de reeks gevolgd door de woorden die we moeten vervangen uit de tabel.

Code:

 Sub VBA_Replace2 () Dim X zo lang X = bereik ("A1000"). Einde (xlUp). Rij dim Y zo lang voor Y = 2 tot X bereik ("B" & Y). Waarde = Vervangen (bereik ("A "& Y), " Delhi ", " New Delhi ") Volgende Y End Sub

Stap 9: compileer nu de volledige code en voer deze uit. Zodra we dat doen zullen we het woord "Delhi" uit Zin A nu vervangen door werk "New Delhi" en de hele zin wordt gekopieerd naar Zin B.

We kunnen een reeks woorden of een zin selecteren en deze vervangen door de vereiste letters of zinnen.

VBA-vervangfunctie - Voorbeeld # 2

Er is een andere manier om woorden of zinnen te vervangen met behulp van VBA-codering. Hiervoor hebben we een lijst van 7 onderwerpen die steeds worden herhaald. En we moeten die onderwerpnamen vervangen door serienummers toe te voegen voor elke onderwerpnaam, zoals hieronder getoond.

Zoals we in de bovenstaande schermafbeelding kunnen zien, heeft kolom E unieke namen van de onderwerpen die zich in kolom B bevinden en kolom F heeft de onderwerpnamen met een serienummer aan het begin ervan.

Volg de onderstaande stappen om de functie Vervangen in VBA te gebruiken.

Stap 1: Ga nu naar VBA en voeg vanuit het menu Invoegen een nieuwe module toe. Zodra we het hebben, begin je de Subcategorie te schrijven in de naam van een functie die wordt uitgevoerd zoals hieronder wordt getoond.

Code:

 Sub VBA_Replace () End Sub

Stap 2: Beschouw nu een dimensie Rng als bereik, InputRng als bereik en ReplaceRng als bereik. U kunt andere letters of woorden kiezen om bereiken te definiëren.

Code:

 Sub VBA_Replace () Dim Rng As Range Dim InputRng As Range, ReplaceRng As Range End Sub

Stap 3: Gebruik nu xTitleId als dialoogvenster en geef het een naam. Hier hebben we het " VBA_Replace " genoemd.

Code:

 Sub VBA_Replace () Dim Rng als bereik Dim InputRng als bereik, ReplaceRng als bereik xTitleId = "VBA_Replace" End Sub

Stap 4: wijs nu Application.Selection toe met InputRng, hierdoor kan de geselecteerde applicatie worden gebruikt in VBA.

Code:

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

Stap 5: voeg nu in de volgende regel een InputBox van 3 soorten in, Original range, xTitleId en InputRng. Origineel bereik is een lijst met die onderwerpen die nodig zijn om vervangen te worden, vermeld in kolom B. xTitledId zijn de namen van de dialoogvensters in de uitgevoerde functie. En InputRng is een datatabel in kolom E en F.

Code:

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

Stap 6: wijs nu in de volgende regel een ingang toe voor ReplaceRng en selecteer hiervoor de kolom Bereik vervangen. Rest ervan hetzelfde.

Code:

 Sub VBA_Replace () Dim Rng als bereik Dim InputRng als bereik, ReplaceRng als bereik 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) End Sub

Stap 7: Nu zullen we de Application.Screenupdating-functie gebruiken, omdat de naam zegt dat het wordt gebruikt voor het bijwerken als het ONWAAR is.

Code:

 Sub VBA_Replace () Dim Rng als bereik Dim InputRng als bereik, ReplaceRng als bereik 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

Stap 8: voeg nu een For-Next-lus in . En vervang voor elk Rng-bereik de waarden uit de kolom VervangRng.

Code:

 Sub VBA_Replace () Dim Rng als bereik Dim InputRng als bereik, ReplaceRng als bereik 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 voor elke Rng in ReplaceRng.Columns (1). Cellen Next End Sub

Stap 9: Vervang InputRng eindelijk door de waarden in Rng van het hele blad.

Code:

 Sub VBA_Replace () Dim Rng als bereik Dim InputRng als bereik, ReplaceRng als bereik 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 voor elke Rng in ReplaceRng.Columns (1). Cellen InputRng.Replace what: = Rng.Value, replacement: = Rng.Offset (0, 1) .Waarde, Lookat: = xlWhole Next End Sub

Stap 10: Eens klaar, compileer en voer de code uit. We krijgen een dialoogvenster op naam van VBA_Replace. Selecteer hier de lijst met onderwerpen die we moeten vervangen als Origineel bereik en klik op OK. Dan zullen we een ander vak krijgen en daar het bereik Vervangen selecteren van E2 tot F8 . Deze tabel bevat de gegevens die moeten worden vervangen en klik op OK.

We krijgen Gegevens in kolom B die worden vervangen door de gegevens in kolom F met serienummer.

Voor een overzicht heeft kolom E unieke onderwerpnamen. En kolom F heeft onderwerpnamen met serienummers. Dus deze gegevens in kolom B worden vervangen door gegevens in kolom F.

Voordelen van VBA Replace Function

  • We kunnen meerdere woorden of zinnen in één schot vervangen.
  • Er is geen limiet aan woorden of tekst waarmee we niet kunnen vervangen.
  • Syntaxis van vervangen in VBA is net zo eenvoudig als het gebruik van SumIf-functie in Excel.
  • VBA Vervangen zoals getoond in voorbeeld-1 is de eenvoudigste manier om toe te passen.

Dingen om te onthouden

  • Opslaan als Marco Ingeschakeld Excel om te voorkomen dat geschreven code in VBA verloren gaat.
  • Houd de dimensies altijd zo in overweging dat er een toegevoegde waarde wordt gecreëerd terwijl we die in code selecteren.
  • Zorg ervoor dat u het hele bereik van vellen selecteert zoals getoond in voorbeeld-2 als het beperkt is om het resultaat te krijgen. Of u kunt een beperkt celbereik definiëren en selecteren dat zou worden gebruikt onder de functie Vervangen.

Aanbevolen artikelen

Dit is een handleiding voor de VBA-vervangfunctie geweest. Hier hebben we VBA vervangen besproken en het gebruik van Excel VBA vervangen, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -

  1. Volledige tutorials over VBA Loops
  2. Hoe VBA Do While Loop te gebruiken?
  3. Wat is VBA-nummerindeling?
  4. Hoe VBA MsgBox te maken?

Categorie:

VBA