VLookup in VBA Excel

Vlookup is een werkbladfunctie in Excel, maar het kan ook worden gebruikt in VBA, de functionaliteit van Vlookup is vergelijkbaar met de functionaliteit in VBA en beide in het werkblad, aangezien het een werkbladfunctie is, is de methode om Vlookup in VBA te gebruiken via Application. methode en de argumenten blijven hetzelfde.

VERT.ZOEKEN Functie in Excel VBA

De functie VERT.ZOEKEN in Excel wordt gebruikt om een ​​waarde in een array te zoeken en de bijbehorende waarde uit een andere kolom te retourneren. De waarde om te zoeken moet aanwezig zijn in de eerste kolom. Het is ook vereist om te vermelden of u naar een exacte overeenkomst of een geschatte overeenkomst wilt zoeken. De werkbladfunctie VERT.ZOEKEN kan worden gebruikt in VBA-codering. De functie is niet ingebouwd in VBA en kan dus alleen worden aangeroepen via het werkblad.

De functie VERT.ZOEKEN in Excel heeft de volgende syntaxis:

Waarin lookup_value de waarde is waarnaar moet worden gezocht, table_arrray de tabel is, col_index_num het kolomnummer van de geretourneerde waarde, range_lookup geeft aan of de overeenkomst exact of bij benadering is. range_lookup kan TRUE / FALSE of 0/1 zijn.

In VBA-code kan de functie VERT.ZOEKEN worden gebruikt als:

Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)

Hoe VLookup te gebruiken in Excel VBA?

Hieronder staan ​​enkele voorbeelden van VLookup-code in Excel VBA.

U kunt deze VLookup in Excel VBA-sjabloon hier downloaden - VLookup in Excel VBA-sjabloon

VLookup-code in Excel VBA Voorbeeld # 1

Laten we eens kijken hoe we de werkbladfunctie VERT.ZOEKEN in Excel VBA kunnen noemen.

Stel dat je een gegevens hebt van de studentenkaart, naam en het gemiddelde cijfer dat ze hebben behaald.

Nu wilt u de cijfers opzoeken die een student heeft behaald met ID 11004.

Volg de volgende stappen om de waarde op te zoeken:

  • Ga naar het tabblad Ontwikkelaar en klik op Visual Basic.

  • Ga onder het VBA-venster naar Invoegen en klik op Module.

  • Schrijf nu de VBA VERT.ZOEKEN-code. De volgende VBA VERT.ZOEKEN-code kan worden gebruikt.

Sub vlookup1 ()

Dim student_id As Long

Dim markeert As Long

student_id = 11004

Set myrange = Range ("B4: D8")

marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Einde Sub

Definieer eerst een student-ID, de waarde die moet worden opgezocht. Daarom definiëren we,

student_id = 11004

Vervolgens definiëren we het bereik waarin de waarde en de retourwaarde bestaan. Omdat onze gegevens aanwezig zijn in de cellen B4: D8, definiëren we een bereik-myrange als:

Set myrange = Range ("B4: D8")

Ten slotte voeren we de functie VERT.ZOEKEN in met behulp van de werkbladfunctie in een variabele, markeert als:

marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Om de markeringen in een berichtvenster af te drukken, gebruiken we het volgende commando:

MsgBox "Student met ID:" & student_id & "verkregen" & cijfers & "cijfers"

Het zal terugkeren:

Student met ID: 11004 behaalde 85 punten.

Klik nu op de knop Uitvoeren.

U zult merken dat er een berichtvenster zal verschijnen in het Excel-blad.

VLookup-code in Excel VBA Voorbeeld # 2

Stel dat u de gegevens heeft van namen van werknemers en hun salaris. Deze gegevens krijgen de kolommen B en C. Nu moet u een VBA VERT.ZOEKEN-code schrijven zodat, gegeven een naam van de werknemer in een cel, F4, het salaris van de werknemer wordt geretourneerd in de cel G4.

Laten we de VBA VERT.ZOEKEN-code schrijven.

  1. Definieer het bereik waarin de waarden aanwezig zijn, dwz kolom B en C.

Set myrange = Range ("B: C")

  1. Definieer de naam van de werknemer en voer de naam in vanuit de cel F4.

Set name = Range ("F4")

  1. Definieer salaris als cel G4.

Salaris instellen = bereik ("G4")

  1. Roep nu de functie VERT.ZOEKEN aan met behulp van WorksheetFunction in VBA en voer deze in salaris.Value in. Dit retourneert de waarde (uitvoer van de functie Vlookup) in de cel G4. De volgende syntaxis kan worden gebruikt:

salaris.Value = Application.WorksheetFunction.VLookup (naam, myrange, 2, False)

  1. Start nu de module. De cel G4 bevat het salaris van de werknemer nadat u de VBA VLOOKUP-code hebt uitgevoerd.

Stel dat u de waarde van cel F4 in het werkblad wijzigt in "David" en de code opnieuw uitvoert, dan krijgt u het salaris van David terug.

VLookup-code in Excel VBA Voorbeeld # 3

Stel dat u de gegevens van de werknemer van uw bedrijf heeft, met zijn ID, namen, afdeling en salaris. Als u Vlookup in VBA gebruikt, wilt u de salarisgegevens van een werknemer krijgen met zijn naam en afdeling.

Aangezien de vlookup-functie in Excel de lookup_value in slechts één kolom doorzoekt, wat de eerste kolom is van de table_array, is het vereist dat u eerst een kolom maakt met de "Naam" en "Afdeling" van elke werknemer.

Laten we in VBA de waarden "Naam" en "Afdeling" invoegen in kolom B van het werkblad.

Ga hiervoor naar het tabblad Ontwikkelaar en klik op Visual Basic. Ga dan naar invoegen en klik op Module om een ​​nieuwe module te starten.

Laten we nu code schrijven, zodat kolom B de waarden van kolom D (naam) en kolom E bevat.

De syntaxis wordt gegeven als:

Gebruik eerst een 'for'-lus van i = 4, aangezien de waarden in dit geval vanaf de 4e rij beginnen. De lus zal doorgaan tot het einde van de laatste rij van kolom C. De variabele i kan dus worden gebruikt als een rijnummer binnen de 'for'-lus.

Voer vervolgens de waarde in die moet worden toegewezen aan Cel (rijnummer, kolom B), die kan worden opgegeven als Cellen (i, "B"). Waarde, als Cel (rijnummer, kolom D) & "_" & Cel (rijnummer, kolom E ).

Stel dat u de cel B5 = D5 & "_" & E5 wilt toewijzen, dan kunt u de code eenvoudig gebruiken als:

Cellen (5, "B"). Waarde = cellen (5, "D"). Waarde & "_" en cellen (5, "E"). Waarde

Laten we nu zoeken naar de opzoekwaarde in de array B5: E24, u moet eerst de opzoekwaarde invoeren. Laten we de waarde (naam en afdeling) van de gebruiker overnemen. Om dit te doen,

  1. definieer drie variabelen, naam, afdeling en lookup_val als een string.
  2. Neem de naaminvoer van de gebruiker over. Gebruik de code:

name = InputBox ("Voer de naam van de medewerker in")

De inhoud in het invoervak ​​"Enter the .." wordt weergegeven in het promptvenster wanneer u de code uitvoert. De string die in de prompt wordt ingevoerd, wordt toegewezen aan de naamvariabele.

  1. Neem de afdeling over van de gebruiker. Het kan op dezelfde manier worden gedaan als hierboven.

department = InputBox ("Vul de afdeling van de medewerker in")

  1. Wijs de naam en afdeling toe met "_" als scheidingsteken voor de variabele lookup_val met behulp van de volgende syntaxis:

lookup_val = naam & “_” & afdeling

  1. Schrijf de vlookup-syntaxis om te zoeken naar de lookup_val in bereik B5: E24 retourneer het in een variabel salaris.

Initialiseer het variabel salaris:

Dim salaris zo lang

Gebruik de functie Vlookup om de lookup_val te vinden. De table_array kan worden opgegeven als Range (“B: F”) en het salaris staat in de 5e kolom. De volgende syntaxis kan dus worden gebruikt:

salaris = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)

  1. Gebruik de syntaxis om het salaris in een berichtvenster af te drukken:

MsgBox (Het salaris van de werknemer is "& salaris)

Voer nu de code uit. Er verschijnt een promptvenster in het werkblad waarin u de naam kunt invoeren. Nadat je de naam hebt ingevoerd (Say Sashi) en klik op OK.

Het opent een ander vak waarin u de afdeling kunt invoeren. Zeg IT nadat u de afdeling bent binnengegaan.

Het zal het salaris van de werknemer afdrukken.

Als de Vlookup een medewerker met de naam en afdeling kan vinden, geeft deze een foutmelding. Stel dat u de naam “Vishnu” en de afdeling “IT” opgeeft, dan zal Run-time error '1004' worden geretourneerd.

Om dit probleem op te lossen, kunt u in de code specificeren dat bij dit type fout in plaats daarvan "Waarde niet gevonden" wordt afgedrukt. Om dit te doen,

  1. Gebruik de volgende code voordat u de vlookup-syntaxis gebruikt:

Bij fout GoTo-bericht

Controleren:

De volgcode (van Check :) wordt gecontroleerd en als deze een fout ontvangt, gaat deze naar de instructie "bericht"

  1. Geef aan het einde van de code (Before End Sub) op dat als het foutnummer 1004 is, u afdrukt in het berichtvenster "Werknemersgegevens niet aanwezig". Dit kan gedaan worden met behulp van de syntaxis:

Bericht:

Als Err.Number = 1004 Then

MsgBox ('Werknemersgegevens niet aanwezig')

Stop als

Module 1:

Sub vlookup3 ()

For i = 4 To Cells (Rows.Count, "C"). End (xlUp) .Row

Cellen (i, "B"). Waarde = cellen (i, "D"). Waarde & "_" & cellen (i, "E"). Waarde

Volgende iDim-naam As String

Dim afdeling As String

Dim lookup_val As String

Dim salaris As Longname = InputBox ("Voer de naam van de werknemer in")

department = InputBox ("Vul de afdeling van de medewerker in")

lookup_val = naam & “_” & departmentOn Fout GoTo-bericht

controleren:

salaris = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)

MsgBox ("Het salaris van de werknemer is" & salaris) Bericht:

Als Err.Number = 1004 Then

MsgBox ('Werknemersgegevens niet aanwezig')

End IfEnd Sub

Dingen om te onthouden over VLookup in Excel VBA

  • De Vlookup-functie kan in Excel VBA worden aangeroepen met behulp van WorksheetFunction.
  • De syntaxis van de vlookup-functie blijft hetzelfde in Excel VBA.
  • Wanneer VBA vlookup-code de lookup_value niet kan vinden, geeft deze een 1004-foutmelding.
  • De fout in de vlookup-functie kan worden beheerd met behulp van een goto-instructie als deze een fout retourneert.