Vlookup är en kalkylbladfunktion i Excel men den kan också användas i VBA, funktionaliteten hos Vlookup liknar funktionaliteten i VBA och i kalkylbladet båda, eftersom det är en kalkylfunktion är metoden att använda Vlookup i VBA genom Application.WorksheetFunction metod och argumenten förblir desamma.
VLOOKUP-funktion i Excel VBA
VLOOKUP-funktionen i Excel används för att söka ett värde i en matris och returnera motsvarande värde från en annan kolumn. Värdet att söka ska finnas i den första kolumnen. Det måste också nämnas om man ska leta efter en exakt matchning eller en ungefärlig matchning. Kalkylbladets funktion VLOOKUP kan användas i VBA-kodning. Funktionen är inte inbyggd i VBA och kan därför bara ringa med hjälp av kalkylbladet.
VLOOKUP-funktionen i Excel har följande syntax:
I vilken, lookup_value är det värde du ska leta efter, table_arrray är tabellen, col_index_num är kolumnnumret för returvärdet, range_lookup betyder om matchningen är exakt eller ungefärlig. range_lookup kan vara SANT / FALSK eller 0/1.
I VBA-kod kan VLOOKUP-funktionen användas som:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Hur använder jag VLookup i Excel VBA?
Nedan följer några exempel på VLookup-kod i Excel VBA.
Du kan ladda ner den här VLookup i Excel VBA-mall här - VLookup i Excel VBA-mall
VLookup-kod i Excel VBA Exempel # 1
Låt oss se hur vi kan kalla kalkylbladets funktion VLOOKUP i Excel VBA.
Antag att du har uppgifter om studenternas ID, namn och genomsnittliga poäng.
Nu vill du slå upp de betyg som en student har fått med ID 11004.
Följ följande steg för att leta efter värdet:
- Gå till fliken Developer och klicka på Visual Basic.
- Under VBA-fönstret, gå till Infoga och klicka på Modul.
- Skriv nu VBA VLOOKUP-koden. Följande VBA VLOOKUP-kod kan användas.
Sub vlookup1 ()
Dim student_id Så länge
Svaga märken så länge
student_id = 11004
Ställ in myrange = Range (“B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Avsluta sub
Först och främst definierar du ett student-id, vilket är värdet du ska slå upp. Därför definierar vi,
student_id = 11004
Därefter definierar vi det område inom vilket värdet och returvärdet finns. Eftersom våra data finns i cellerna B4: D8 definierar vi ett intervall-intervall som:
Ställ in myrange = Range (“B4: D8”)
Slutligen matar vi in VLOOKUP-funktionen med kalkylbladets funktion i en variabel, markerar som:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
För att skriva ut märkena i en meddelandefält, låt oss använda följande kommando:
MsgBox “Student med ID:” & student_id & ”erhållit” & betyg & ”märken”
Det kommer att återvända:
Student med ID: 11004 fick 85 poäng.
Klicka nu på kör-knappen.
Du kommer att märka att en meddelanderuta visas i Excel-arket.
VLookup-kod i Excel VBA Exempel # 2
Antag att du har uppgifter om anställdas namn och deras lön. Dessa data ges kolumnerna B och C. Nu måste du skriva en VBA VLOOKUP-kod så att med tanke på namnet på den anställde i en cell, F4, kommer anställdens lön att returneras i cellen G4.
Låt oss skriva VBA VLOOKUP-koden.
- Definiera det område inom vilket värdena finns, dvs. kolumn B och C.
Ställ in myrange = Range (“B: C”)
- Definiera namnet på den anställde och mata in namnet från cellen F4.
Set name = Range (“F4”)
- Definiera lön som cell G4.
Ställ in lön = intervall (“G4”)
- Ring nu VLOOKUP-funktionen med WorksheetFunction i VBA och mata in den i lön. Detta returnerar värdet (utdata från Vlookup-funktionen) i cellen G4. Följande syntax kan användas:
lön.Värde = Application.WorksheetFunction.VLookup (namn, myrange, 2, Falskt)
- Kör nu modulen. Cellen G4 innehåller lönen för den anställde efter att du kör VBA VLOOKUP-koden.
Antag att du ändrar värdet på cell F4 till "David" i kalkylbladet och kör igen koden, det returnerar Davids lön.
VLookup-kod i Excel VBA-exempel # 3
Antag att du har uppgifterna om den anställde i ditt företag, med deras ID, namn, avdelning och lön. Med Vlookup i VBA vill du få löneuppgifter för en anställd med hjälp av hans namn och avdelning.
Eftersom vlookup-funktionen i excel söker efter uppslagsvärde i endast en enskild kolumn, vilket är den första kolumnen i tabellarrayen, måste du först skapa en kolumn som innehåller "Namn" och "Avdelning" för varje anställd.
Låt oss i VBA infoga värdena "Namn" och "Avdelning" i kolumn B i kalkylbladet.
För att göra detta, gå till fliken Developer och klicka på Visual Basic. Gå sedan till infoga och klicka på Modul för att starta en ny modul.
Låt oss nu skriva kod så att kolumn B innehåller värdena för kolumn D (namn) och kolumn E.
Syntaxen ges som:
För det första använder du en 'for' -slinga från i = 4 eftersom värdena börjar i fjärde raden i detta fall. Slingan fortsätter till slutet av den sista raden i kolumn C. Så variabeln i kan användas som ett radnummer inuti 'för' slingan.
Ange sedan det värde som ska tilldelas för Cell (radnummer, kolumn B), som kan ges som celler (i, “B”). Värde, som Cell (radnummer, kolumn D) & “_” & Cell (radnummer, kolumn E ).
Antag att du vill tilldela cellen B5 = D5 & “_” & E5, du kan helt enkelt använda koden som:
Celler (5, "B"). Värde = Celler (5, "D"). Värde & "_" & Celler (5, "E"). Värde
Låt oss nu leta efter uppslagsvärdet i matrisen B5: E24, du måste först ange uppslagsvärdet. Låt oss ta värdet (namn och avdelning) från användaren. Att göra detta,
- definiera tre variabler, namn, avdelning och lookup_val som en sträng.
- Ta namnet från användaren. Använd koden:
namn = InputBox (“Ange namnet på den anställde”)
Innehållet i inmatningsrutan "Ange .." kommer att visas i meddelandefältet när du kör koden. Strängen som anges i prompten tilldelas namnvariabeln.
- Ta avdelningen från användaren. Det kan göras på samma sätt som ovan.
avdelning = InputBox ("Ange avdelningen för den anställde")
- Tilldela namn och avdelning med “_” som en avgränsare till variabeln lookup_val med följande syntax:
lookup_val = namn & “_” & avdelning
- Skriv vlookup-syntaxen för att söka efter lookup_val i intervall B5: E24 returnerar den i en rörlig lön.
Initiera den rörliga lönen:
Dim lön så länge
Använd Vlookup-funktionen för att hitta lookup_val. Table_array kan anges som Range (“B: F”) och lönen finns i 5: e kolumnen. Följande syntax kan således användas:
lön = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- För att skriva ut lönen i ett meddelandefält, använd syntaksen:
MsgBox (anställdas lön är ”och lön)
Kör nu koden. En snabbruta visas i kalkylbladet där du kan ange namnet. När du har angett namnet (Say Sashi) och klicka på OK.
Det öppnar en annan ruta där du kan komma in i avdelningen. När du har gått in på avdelningen, säg IT.
Det kommer att skriva ut lönen för den anställde.
Om Vlookup kan hitta någon anställd med namn och avdelning kommer det att ge ett fel. Antag att du ger namnet "Vishnu" och avdelningen "IT", det kommer att returnera körtidsfel '1004'.
För att åtgärda detta problem kan du ange i koden att i stället skriva ut "Värde hittades inte" på denna typ av fel. Att göra detta,
- Innan du använder vlookup-syntaxen, använd följande kod-
Vid fel GoTo-meddelande
Kontrollera:
Den efterföljande koden (av Check :) kommer att övervakas, och om den får något fel, kommer den att gå till meddelandet "meddelande"
- Ange i slutet av koden (Before End Sub) att om felnumret är 1004, skriv sedan ut i meddelandefältet ”Anställdata ej närvarande”. Detta kan göras med hjälp av syntaksen:
Meddelande:
Om felnummer = 1004 då
MsgBox (”Uppgifter om anställda finns inte”)
Avsluta om
Modul 1:
Sub vlookup3 ()För i = 4 till celler (rader. Antal, "C"). Slut (xlUp). Rad
Celler (i, “B”). Värde = Celler (i, “D”). Värde & “_” & Celler (i, “E”). Värde
Nästa iDim-namn som sträng
Dim avdelning som sträng
Dim lookup_val As String
Dim lön som långnamn = InputBox (“Ange namnet på den anställde”)
avdelning = InputBox ("Ange avdelningen för den anställde")
lookup_val = name & “_” & departmentOn Error GoTo Message
kontrollera:
lön = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
MsgBox (“Den anställdas lön är” och lön) Meddelande:
Om felnummer = 1004 då
MsgBox (”Uppgifter om anställda finns inte”)
Avsluta om
Saker att komma ihåg om VLookup i Excel VBA
- Vlookup-funktionen kan anropas i Excel VBA med hjälp av WorksheetFunction.
- Syntaksen för vlookup-funktionen förblir densamma i Excel VBA.
- När VBA vlookup-kod inte kan hitta lookup_value, kommer det att ge 1004-fel.
- Felet i vlookup-funktionen kan hanteras med ett goto-uttalande om det returnerar ett fel.
