Leer uw Vlookups te automatiseren met Excel VBA

Lezers zoals jij steunen MUO. Wanneer u een aankoop doet via links op onze site, kunnen we een aangesloten commissie verdienen. Lees verder.

Vlookup is een essentiële functie binnen Excel en is een essentieel onderdeel geworden van gegevensverwerking. Het biedt een deel van de functionaliteit die u gewoonlijk zou associëren met een volwaardige database.

Maar wat als u deze functie wilt automatiseren? Ja, dat is mogelijk, vooral als u weet hoe u de native automatiseringstaal van Excel, VBA, moet gebruiken. Hier leest u hoe u de vlookup-functie in Excel VBA kunt automatiseren.

MAAK GEBRUIK VAN DE VIDEO VAN DE DAG

De syntaxis van Vlookup

De syntaxis van de vlookup-functie is vrij eenvoudig, maar heeft vier aspecten waarop u zich moet concentreren, zelfs als u deze automatiseert in Excel VBA.

 =vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

De argumenten van de functie hebben de volgende betekenis:

opzoekwaarde: Dit is de primaire waarde die u wilt opzoeken in de opzoekarray.
lookup_array: Dit zijn de brongegevens, die de lookup_value als referentie zal gebruiken.
kolom_index: Kolom waaruit de waarde moet worden geretourneerd.
exact_match/partial_match: Gebruik 0 of 1 om het zoektype te bepalen.

Data voorbereiding

Deze gegevensset bestaat uit twee delen: een opzoektabel en de bestemmingsgegevenspunten.

hoe de helderheid van Windows 10 te verlagen

De opzoektabel bestaat uit drie kolommen, met gegevens die de velden Subcategorie en Productnaam invullen:

Gegevenstabel in MS Excel met vooraf ingevulde rijen

De bestemmingstabel heeft overeenkomende kolommen, zonder gegevens over subcategorieën of productnamen. Merk op dat de waarden in de kolom Bestellings-ID ook aanwezig zijn in dezelfde kolom in de opzoektabel:

Zoek waarden op in een Excel-spreadsheet

Vlookup rechtstreeks gebruiken in een Excel-spreadsheet

Om de bestemmingskolommen, B en C, te vullen, kan dat gebruik de vlookup-functie van Excel .

In cel B2 , voer de volgende formule in:

 =VLOOKUP($A2, $F:$H, 2, 0)

Zo ook in cel C2 , voer deze formule in:

 =VLOOKUP($A2, $F:$H, 3, 0)

U kunt de formule naar beneden slepen van cel B2 tot het einde van de kolom, B17. Herhaal dit proces ook voor kolom C. De waarden voor elk volgend item worden automatisch bijgewerkt in beide kolommen.

De waarden die u doorgeeft aan de vlookup-functie zijn:

A2 : De opzoekwaarde bevindt zich in deze cel.
F3:H17 : Dit bestaat uit het opzoekbereik.
23 : De referentiekolommen waaruit de waarde moet worden opgehaald.
0 : Geeft een exacte overeenkomst aan.

Excel VBA's Vlookup-functie

De vlookup-functie heeft veel overeenkomsten, of u deze nu rechtstreeks in Excel of via VBA gebruikt. Of u nu een data-analist bent die zich bezighoudt met rijen gegevens, of een voorraadbeheerder die regelmatig met nieuwe producten te maken heeft, u kunt profiteren door vlookup te gebruiken.

Wanneer u met een dynamisch opzoekbereik werkt, is het altijd het beste om uw formules te automatiseren, om te voorkomen dat formules herhaaldelijk in Excel worden toegepast. Door uw Vlookup-functie in VBA te automatiseren, kunt u met één klik berekeningen met meerdere kolommen uitvoeren.

1. Voer de vereiste instellingen uit

Begin met het openen van de coderingseditor (druk op Alt+F11 of navigeer naar de Ontwikkelaar tabblad) in een nieuwe Excel-werkmap en voeg een module toe om te beginnen met het schrijven van uw code. Voeg in de code-editor de volgende regels toe bovenaan het codeervenster om een subroutine te maken:

  Sub vlookup_fn1()End Sub

De subroutine is een container voor uw VBA-code en is essentieel om deze succesvol uit te voeren. Een alternatief is om maak een VBA-gebruikersformulier om uw gebruikersinterface interactiever te maken.

2. Declareer uw variabelen en creëer uw referentiebereiken

Eerst moet u de variabele gegevenstypen declareren met behulp van de Afm stelling:

  Dim i as integer, lastrow as long

Maak vervolgens een laatste rij variabele, om de waarde van de laatst bevolkte rij binnen uw opzoekbereik op te slaan. De lastrow-functie gebruikt de einde(xldown) functie om de eindrijreferentie binnen het opgegeven bereik te berekenen.

In dit geval slaat de lastrow-variabele de laatste bevolkte rijwaarde van het opzoekbereik op, 17.

xbox one bedrade controller werkt niet

  lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

In het bovenstaande voorbeeld strekt de bestemmingstabel zich uit van A2:C17 , terwijl de brontabel zich uitstrekt van F2:H17 . De vlookup-formule doorloopt elke waarde die is opgeslagen in kolom A, zoekt deze op in de brontabel en plakt de overeenkomende vermeldingen in kolommen B en C.

Voordat u echter in de lus springt, moet u de bereikwaarden opslaan in een nieuwe variabele ( mijn_bereik ), als volgt:

  my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

U moet expliciet de begincelverwijzing definiëren ( F3 ) en de eindkolomreferentie ( H ). VBA voegt automatisch de rijwaarde toe om de opzoekarray te voltooien.

3. Schrijf een lus om door elke opzoekwaarde te bladeren

Voordat u de lus schrijft, definieert u de waarde van de startrij, 2 . Het definiëren van een startwaarde voor je lus is essentieel omdat je te maken hebt met dynamische elementen. In de bestemmingstabel is rij 2 de eerste rij met gegevens. Wijzig deze waarde als uw gegevens in een andere rij beginnen.

  i = 2

U kunt een doen terwijl lus om elke rij te verwerken, beginnend bij rij 2 en eindigend bij rij 17. Net als de rest van de code is dit aspect dynamisch; de lus wordt uitgevoerd totdat de voorwaarde onwaar is. Gebruik de voorwaarde om te controleren op een niet-lege waarde in de eerste cel van de huidige rij.

  Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Binnen de lus kunt u de VLookup-functie aanroepen om de cellen te vullen:

een nieuw e-mailadres krijgen

  Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Deze instructies stellen de waarde in van de cellen in de huidige rij, respectievelijk in kolom 2 en 3. Ze gebruiken de WerkbladFunctie bezwaar maken om de VLookup functie, waarbij de corresponderende waarde uit kolom 1 wordt doorgegeven om naar te zoeken. Ze geven ook het bereik door dat u eerder hebt gedefinieerd en de index van de relevante kolom om de uiteindelijke waarde op te halen.

De vlookup-code is klaar, maar je moet nog steeds de rijvariabele elke keer rond de lus verhogen:

  i = i + 1

Elke keer dat de lus wordt uitgevoerd, wordt de waarde van verhoogd i met 1. Onthoud dat de beginwaarde van de rij 2 is en dat de verhoging elke keer dat de lus wordt uitgevoerd, wordt verhoogd. Gebruik de lus trefwoord om het einde van het codeblok van de lus in te stellen.

Wanneer u de volledige code uitvoert, worden de resultaten in beide kolommen ingevuld op basis van de waarden in de brontabel.

Uitvoer in tabelvorm in Excel-spreadsheet

Hier is de volledige code ter referentie:

  Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Maak een knop om de code uit te voeren

Met de code uit de weg, kunt u een knop in uw Excel-werkmap maken om deze met een enkele klik uit te voeren. Plaats uw gewenste vorm op een leeg Excel-blad, klik er met de rechtermuisknop op en klik op de Macro toewijzen keuze.

Rechthoekknop op een Excel-spreadsheet met een lijst met opties

Selecteer in het dialoogvenster de naam van uw subroutine en klik op OK .

Macrobox geopend op een Excel-spreadsheet

Wanneer u uw code wilt uitvoeren, klikt u op de knop om te zien hoe de gegevens onmiddellijk worden ingevuld.

Excel VBA gebruiken om opzoekfuncties te automatiseren

Excel VBA is een flexibele taal, die goed is afgestemd om automatisering binnen de verschillende facetten van MS Excel te vergemakkelijken. Er zijn veel opties in MS Excel VBA, van het automatiseren van Excel-functies tot het automatisch maken van uitgebreide draaitabellen.

Wanneer u met verschillende Excel-segmenten werkt, kunt u experimenteren met verschillende opties om uw leven gemakkelijker en efficiënter te maken.