Excel VBA Zoeken en vervangen

Heb je er ooit aan gedacht om de functie Zoeken en vervangen in Excel te automatiseren. In VBA kunnen we een macro maken die kan worden gebruikt voor het vinden en vervangen van alles wat we normaal doen in Excel door op Ctrl + H te drukken. Met behulp van VBA Zoeken en vervangen kunnen we de vondst van elk woord automatiseren en vervangen door andere vervangers. Dit helpt wanneer we dezelfde activiteit meerdere keren moeten uitvoeren.

Hoe woorden te vinden en te vervangen in Excel VBA?

Hieronder staan ​​de verschillende voorbeelden om de woorden in Excel te vinden en te vervangen met behulp van VBA-code.

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

VBA Zoeken en vervangen - Voorbeeld # 1

In een heel eenvoudig voorbeeld hebben we een gegevensset die bestaat uit een naam. En sommige namen worden herhaald zoals hieronder getoond.

Nu maken we een code, waarmee we alle namen vinden die worden herhaald en vervangen door iets anders. Volg hiervoor de onderstaande stappen.

Stap 1: Ga naar het menu Invoegen in VBA en selecteer de optie Module zoals hieronder weergegeven.

Stap 2: Zodra we dat doen, krijgen we een nieuwe module geopend. Schrijf in die module de subcategorie in de naam van VBA Zoeken en vervangen of u kunt elke naam van die subcategorie kiezen.

Code:

 Sub Find_Replace1 () Einde Sub

Stap 3: Omdat we de gegevens in kolom B van cel B2 tot B10 hebben, zullen we dat bereik eerst selecteren uit B2: B10.

Code:

 Sub Find_Replace1 () Bereik ("B2: B10"). Vervang einde Sub

Stap 4: Om iets te vervangen, moeten we dat woord zoeken. Ook hier zullen we eerst de functie Vervangen selecteren om te kijken wat we moeten vervangen.

Code:

 Sub Find_Replace1 () Bereik ("B2: B10"). Vervang einde Sub

Volgens de syntaxis van de functie Vervangen, vinden we wat we moeten vervangen.

Stap 5: Laten we de voornaam van de lijst " Ben " selecteren als wat we moeten vervangen.

Code:

 Sub Find_Replace1 () Bereik ("B2: B10"). Vervang What: = "Ben" End Sub

Stap 6: Kies nu het vervangende woord waarmee we het geselecteerde woord moeten vervangen. Hier hebben we ervoor gekozen om de naam Ben te vervangen door Sam, die wordt genoemd als Vervanging zoals hieronder weergegeven.

Code:

 Sub Find_Replace1 () Bereik ("B2: B10"). Wat vervangen: = "Ben", Vervanging: = "Sam" End Sub

Stap 7: compileer nu de code en voer deze uit door op de knop Afspelen te klikken of door op F5 te drukken, zoals hieronder wordt weergegeven.

We zullen zien dat alle cellen die de naam bevatten zoals Ben nu is vervangen door de naam Sam . En dat wordt ook in een gele kleur gemarkeerd.

VBA Zoeken en vervangen - Voorbeeld # 2

We hebben allemaal misschien een situatie meegemaakt waarin we probeerden enkele woorden te vinden en te vervangen door hoofdlettergevoelige letters. Stel dat we in een lijst alleen dat woord willen vervangen dat enkele kleine letters of hoofdletters bevat. En als we het proces gebruiken dat we in voorbeeld-1 hebben gezien, zouden we uiteindelijk alle vergelijkbare woorden in die lijst vervangen door de vereiste. Dit proces kan niet worden uitgevoerd met behulp van de functie Zoeken en vervangen (Ctrl + H) van Excel. Hiervoor hebben we een voorziening in VBA.

Laten we de onderstaande gegevens hiervoor bekijken. Zoals we kunnen zien, hebben we met opzet een woord BEN toegevoegd in hoofdletters in cel B2.

Volg de onderstaande stappen om het woord BEN te vinden en te vervangen .

Stap 1: Start de subcategorie van VBA Zoeken en vervangen daar zoals hieronder getoond.

Code:

 Sub Find_Replace2 () Einde Sub

Stap 2: Selecteer de lijst als Bereik van B2 tot B10.

Code:

 Sub Find_Replace2 () Bereik ("B2: B10") Einde Sub

Stap 3: Kies nu het exacte woord dat we willen vervangen. Hier hebben we BEN geselecteerd die zich in cel B2 bevindt.

Code:

 Sub Find_Replace2 () Bereik ("B2: B10"). Vervang What: = "BEN" End Sub

Stap 4: Nogmaals, we hebben hetzelfde woord geselecteerd dat we in voorbeeld-1 hebben gezien als vervanging van BEN als Sam .

Code:

 Sub Find_Replace2 () Bereik ("B2: B10"). Wat vervangen: = "BEN", Vervanging: = "Sam" End Sub

Stap 5: Nu voor het vervangen van het exacte woord BEN, moeten we MatchCase gebruiken als dat TRUE wordt, zoals hieronder getoond.

Code:

 Sub Find_Replace2 () Bereik ("B2: B10"). Wat vervangen: = "BEN", Vervanging: = "Sam", MatchCase: = True End Sub

De MatchCase helpt ons bij het identificeren van dat woord dat we willen vervangen door een exacte overeenkomst. Hier is het woord dat we hebben geselecteerd BEN in hoofdletters en lijst bevat ook andere vergelijkbare woorden, maar in de juiste hoofdletters.

Stap 6: Voer nu de code uit door op de knop Afspelen te klikken of door op F5 te drukken.

We zullen zien dat het woord BEN dat zich in cel B2 bevond nu wordt vervangen door het woord " Sam ", gemarkeerd in gele kleur. En de vergelijkbare woorden Ben die zich respectievelijk in cellen B5 en B8 bevinden, zijn nog steeds ongewijzigd.

Dit is hoe exact zoeken werkt.

Stap 7: Nu zullen we de toegevoegde MatchCase uit de bovenstaande code verwijderen en kijken hoe dit zou werken.

Code:

 Sub Find_Replace2 () Bereik ("B2: B10"). Wat vervangen: = "BEN", Vervanging: = "Sam", MatchCase: = True End Sub

Stap 8: compileer en voer de code opnieuw uit.

We zullen zien dat de code alle cellen met het woord Ben heeft vervangen, zoals hierboven weergegeven. Dit betekent dat na het verwijderen van MatchCase de code zal werken zoals we voorbeeld 1 hebben gezien.

Voordelen van Excel VBA Zoeken en vervangen

  • Veel geleerd is dat we hoofdlettergevoelige woorden en celinhoud kunnen vervangen met behulp van VBA Zoeken en vervangen zoals getoond in voorbeeld-2.
  • Als de activiteit handmatig wordt uitgevoerd en meerdere keren wordt herhaald, is automatisering een voordeel om zowel tijd als moeite te besparen.
  • Zelfs als we een enorme set gegevens hebben waar we specifieke woorden willen vervangen, kan dat worden gedaan zonder de Excel in omvangrijke bestanden te laten crashen.

Dingen om te onthouden

  • We kunnen een macro maken van de functie Zoeken en vervangen met behulp van de optie Macro opnemen op het tabblad Ontwikkelaar. Dit is de eenvoudigste manier om een ​​macro te maken als u nog niet bekend bent met coderen.
  • Door het bestand in Macro op te slaan, wordt het mogelijk om in de toekomst code te wennen.
  • We kunnen elke vorm van tekst, woord, nummer of teken vervangen door VBA Zoeken en vervangen.

Aanbevolen artikelen

Dit is een handleiding voor VBA Zoeken en vervangen. Hier bespreken we hoe u woorden in Excel kunt vinden en vervangen met behulp van VBA, samen met praktische voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -

  1. VBA Break for Loop
  2. VERVANG de formule in Excel
  3. Een complete gids VBA Find
  4. ZOEK Functie in Excel

Categorie:

VBA