Hoe Vlookup te automatiseren met Excel VBA

Vlookup is een essentiële functie in Excel en is een belangrijk onderdeel geworden van de gegevensverwerking. Het biedt een aantal functies die u kunt combineren met een uitgebreide database.

Als u deze functie echter wilt automatiseren, kan dat, vooral als u weet hoe u de automatiseringstaal moet gebruiken die beschikbaar is in Excel en VBA. Hier leest u hoe u de vlookup-functie in Excel VBA kunt automatiseren .

Vlookup-functieformule in Excel

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

Betekenis van de argumenten in de formule:

• lookup_value: De onderliggende waarde die u in de array wilt opzoeken.
• lookup_array: Brongegevens, lookup_value worden als referentie gebruikt.
• column_index: Kolom retourneert waarde.
• exact_match/partial_match: Gebruik 0 of 1 om het combinatietype op te geven.

VBA Vlookup-functie in Excel

Het gebruik van de Vlookup-functie verschilt niet veel als u deze rechtstreeks in Excel of via VBA gebruikt. Of een data-analist nu rijen informatie verwerkt of een magazijnbeheerder voortdurend met nieuwe producten werkt, de vlookup-functie is handig.

Wanneer u met een dynamisch opzoekbereik werkt, is het automatiseren van de formule het beste, om te voorkomen dat u dezelfde formule meerdere keren in Excel moet manipuleren. Door de functie Vlookup in VBA te automatiseren, kunt u met één klik berekeningen met meerdere kolommen uitvoeren.

Illustratie

Deze gegevensset bestaat uit twee delen: een opzoektabel en doelgegevenspunten. De opzoektabel bevat 3 kolommen, met gegevens in de twee velden Subcategorie en Productnaam:

De doeltabel heeft de juiste kolommen, geen submappen of productnaamgegevens. Houd er rekening mee dat de waarden in de kolom Order ID ook in dezelfde kolom in de opzoektabel verschijnen:

1. Voer de nodige instellingen uit

Begin met het openen van de code-editor (druk op Alt + F11 of navigeer naar het tabblad Ontwikkelaars ) in een nieuwe Excel-werkmap en voeg de module toe om te beginnen met coderen. Voeg in de code-editor de volgende regels toe aan de bovenkant van het programmeervenster om een ​​subroutine te maken:

Sub vlookup_fn1()End Sub

subroutine is een container voor VBA-code en is vereist om deze succesvol uit te voeren. Een ander alternatief is het maken van een VBA-gebruikersformulier om de gebruikersinterface interactiever te maken.

2. Declareer variabelen en maak referentiebereiken

Eerst moet u de gegevenstypen van variabelen declareren met behulp van de opdracht Dim:

Dim i as integer, lastrow as long

Maak vervolgens een variabele lastrow om de waarde van de laatst ingevulde rij in het opzoekbereik op te slaan. De functie lastrow gebruikt de functie end(xldown) om de laatste rijreferentie in een specifiek bereik te berekenen.

In dit voorbeeld bevat de lastrow- variabele de waarde van de laatst gevulde rij in het opzoekbereik, met name 17.

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

In het bovenstaande voorbeeld strekt de doeltabel zich uit van A2:C17 en de brontabel van F2:H17. De vlookup-formule doorloopt elke waarde in kolom A, zoekt deze op in de brontabel en plakt de gecombineerde waarden in de kolommen B en C.

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

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

U moet expliciet de begincelverwijzing (F3) en de eindkolomverwijzing (H) opgeven. VBA voegt automatisch rijwaarden toe om de opzoekarray te voltooien.

3. Schrijf een lus om elke opzoekwaarde te doorlopen

Voordat u de lus schrijft, definieert u de startrijwaarde. 2. Het definiëren van een startwaarde voor de lus is noodzakelijk als u te maken heeft met dynamische componenten. In de doeltabel is rij 2 de eerste rij met gegevens. Wijzig deze waarde als de gegevens in een andere rij beginnen.

i = 2

Je kunt een do-while- lus gebruiken om elke rij te verwerken, beginnend bij rij 2 en eindigend bij rij 17. Net als de rest van de code is dit aspect flexibel; De lus loopt totdat de voorwaarde onwaar is. Gebruik de voorwaarde om te testen 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 kun je de Vlookup-functie aanroepen om de cellen te vullen:

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 opdrachten stellen de waarden in van cellen in de huidige rij, respectievelijk in kolom 2 en 3. Ze gebruiken het WorksheetFunction-object om de Vlookup-functie aan te roepen, waarbij de overeenkomstige waarde uit kolom 1 wordt doorgegeven aan de zoekopdracht. Ze passeren ook het bereik dat u aanvankelijk hebt gedefinieerd en de index van de relevante kolom om de uiteindelijke waarde op te halen.

De vlookup-code is gereed, maar u moet nog steeds de rijvariabele elke keer in de lus verhogen:

i = i + 1

Elke keer dat de lus wordt uitgevoerd, wordt de waarde i met 1 verhoogd . Houd er rekening mee dat de startrijwaarde 2 is. De verhoging vindt plaats elke keer dat de lus wordt uitgevoerd. Gebruik het trefwoord lus om het einde van dit codeblok in te stellen.

Wanneer de volledige code wordt uitgevoerd, worden beide kolommen gevuld met resultaten, op basis van de waarden in de brontabel.

Hieronder vindt u de volledige referentiecode:

 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

U kunt een knop in een Excel-tabel maken om deze met één klik uit te voeren. Plaats de gewenste vorm op het lege Excel-werkblad, klik er met de rechtermuisknop op en klik op de optie Macro toewijzen .

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

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

Hierboven ziet u hoe u Excel VBA kunt gebruiken om opzoekfuncties te automatiseren . Ik hoop dat het artikel nuttig voor je is.