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 geen ingebouwde 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 de VLookup-code in Excel VBA.
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 gegevens hebt over de ID, naam en het gemiddelde cijfer dat ze hebben behaald.
Nu wilt u de cijfers opzoeken die zijn behaald door een student met ID 11004.
Volg de volgende stappen om de waarde op te zoeken:
- Ga naar het tabblad Ontwikkelaars 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 marks As Long
student_id = 11004
Set myrange = Range ("B4: D8")
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End 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:
Een 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 over de namen van werknemers en hun salaris. Deze gegevens krijgen de kolommen B en C. Nu moet u een VBA VERT.ZOEKEN-code schrijven zodat, gezien de 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.
- Definieer het bereik waarin de waarden aanwezig zijn, dat wil zeggen de kolommen B en C.
Set myrange = Range ("B: C")
- Definieer de naam van de werknemer en voer de naam in vanuit de cel F4.
Set name = Range ("F4")
- Definieer salaris als cel G4.
Salaris instellen = bereik ("G4")
- 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)
- 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 verandert in "David" en de code opnieuw uitvoert, en het zal het salaris van David teruggeven.
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 enkele 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 'voor' loop van i = 4, omdat de waarden vanaf de 4 e rij in dit geval. 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 kijken 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,
- definieer drie variabelen, naam, afdeling en lookup_val als een string.
- Neem de naaminvoer van de gebruiker over. Gebruik de code:
name = InputBox ("Voer de naam van de medewerker in")
De inhoud in het invoervak "Voer de…" wordt weergegeven in het promptvenster wanneer u de code uitvoert. De string die in de prompt wordt ingevoerd, wordt toegewezen aan de naamvariabele.
- 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")
- Wijs de naam en afdeling met "_" als scheidingsteken toe aan de variabele lookup_val met behulp van de volgende syntaxis:
lookup_val = naam & “_” & afdeling
- Schrijf de vlookup-syntaxis om te zoeken naar de lookup_val in bereik B5: E24 retourneert 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 gegeven als Range ( “B: F”) en het salaris in de 5 ste kolom. De volgende syntaxis kan dus worden gebruikt:
salaris = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)
- 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,
- 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 "message" -instructie
- Geef aan het einde van de code (Before End Sub) op dat als het foutnummer 1004 is, u in het berichtvak 'Werknemersgegevens niet aanwezig' afdrukt. 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
Cells (i, "B"). Value = Cells (i, "D"). Value & "_ "& 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 (" Voer de afdeling van de werknemer ”)
lookup_val = naam &“ _ ”& departmentOn Fout GoTo Berichtcontrole
:
salaris = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, False)
MsgBox (“ Het salaris van de werknemer is "& Salaris) Bericht:
If 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 de 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.
