Vlookup er en regnearkfunktion i Excel, men den kan også bruges i VBA, funktionaliteten af Vlookup svarer til funktionaliteten i VBA og i regneark begge, da det er en regnearkfunktion, er metoden til at bruge Vlookup i VBA gennem Application.WorksheetFunction metode og argumenterne forbliver de samme.
VLOOKUP-funktion i Excel VBA
VLOOKUP-funktionen i Excel bruges til at søge efter en værdi i en matrix og returnere dens tilsvarende værdi fra en anden kolonne. Værdien, der skal søges, skal være til stede i den første kolonne. Det er også nødvendigt at nævne, om man skal kigge efter et nøjagtigt match eller et omtrentligt match. Regnearkfunktionen VLOOKUP kan bruges i VBA-kodning. Funktionen er ikke indbygget VBA og kan derfor kun ringe ved hjælp af regnearket.
VLOOKUP-funktionen i Excel har følgende syntaks:
I hvilken lookup_value er den værdi, man skal kigge efter, table_arrray er tabellen, col_index_num er kolonnenummeret for returværdien, range_lookup betyder, om matchet er nøjagtigt eller omtrentligt. range_lookup kan være SAND / FALSK eller 0/1.
I VBA-kode kan VLOOKUP-funktionen bruges som:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Hvordan bruges VLookup i Excel VBA?
Nedenfor er nogle eksempler på VLookup-koden i Excel VBA.
VLookup-kode i Excel VBA Eksempel # 1
Lad os se, hvordan vi kan kalde regnearkfunktionen VLOOKUP i Excel VBA.
Antag at du har data om studerendes id, navn og de gennemsnitlige karakterer, de har opnået.
Nu vil du slå de karakterer op, som en studerende har opnået med ID 11004.
Følg følgende trin for at finde værdien:
- Gå til fanen Developer og klik på Visual Basic.
- Under VBA-vinduet skal du gå til Indsæt og klikke på Modul.
- Skriv nu VBA VLOOKUP-koden. Følgende VBA VLOOKUP-kode kan bruges.
Sub vlookup1 ()
Dim student_id Som Long
Dim marks Som Long
student_id = 11004
Set myrange = Range (“B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
For det første skal du definere et elev-id, som er værdien, der skal opslås. Derfor definerer vi,
student_id = 11004
Dernæst definerer vi det område, hvor værdien og returværdien findes. Da vores data er til stede i cellerne B4: D8, definerer vi en rækkevidde som:
Indstil myrange = rækkevidde (“B4: D8”)
Endelig indtaster vi VLOOKUP-funktionen ved hjælp af regnearkfunktionen i en variabel, markerer som:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
For at udskrive mærkerne i en meddelelsesboks, lad os bruge følgende kommando:
MsgBox “Student med ID:” & student_id & ”opnået” & mærker & ”mærker”
Det vender tilbage:
En studerende med ID: 11004 opnåede 85 point.
Klik nu på knappen Kør.
Du vil bemærke, at en meddelelsesboks vises i Excel-arket.
VLookup-kode i Excel VBA Eksempel # 2
Antag at du har dataene om medarbejdernes navne og deres løn. Disse data er givet kolonnerne B og C. Nu skal du skrive en VBA VLOOKUP-kode, således at givet medarbejderens navn i en celle, F4, vil lønnen til medarbejderen blive returneret i cellen G4.
Lad os skrive VBA VLOOKUP-koden.
- Definer det område, hvor værdierne er til stede, dvs. kolonner B og C.
Indstil myrange = rækkevidde (“B: C”)
- Definer navnet på medarbejderen, og indtast navnet fra cellen F4.
Indstil navn = rækkevidde (“F4”)
- Definer løn som celle G4.
Indstil løn = rækkevidde (“G4”)
- Ring nu til VLOOKUP-funktionen ved hjælp af WorksheetFunction i VBA, og indtast den i løn. Værdi. Dette returnerer værdien (output af Vlookup-funktionen) i cellen G4. Følgende syntaks kan bruges:
løn.Value = Application.WorksheetFunction.VLookup (navn, myrange, 2, Falsk)
- Kør nu modulet. Cellen G4 indeholder løn til medarbejderen, når du har kørt VBA VLOOKUP-koden.
Antag at du ændrer værdien af celle F4 til "David" i regnearket og kører koden igen, og den returnerer Davids løn.
VLookup-kode i Excel VBA Eksempel # 3
Antag at du har dataene fra medarbejderen i din virksomhed med deres id, navne, afdeling og løn. Ved hjælp af Vlookup i VBA ønsker du at få lønningsoplysningerne for en medarbejder ved hjælp af hans navn og afdeling.
Da vlookup-funktionen i excel kun søger på opslagsværdien i kun en enkelt kolonne, som er den første kolonne i tabelarrayen, kræves det, at du først opretter en kolonne, der indeholder "Navn" og "Afdeling" for hver medarbejder.
Lad os i VBA indsætte værdierne "Navn" og "Afdeling" i kolonne B i regnearket.
For at gøre dette skal du gå til fanen Udvikler og klikke på Visual Basic. Gå derefter til Indsæt og klik på Modul for at starte et nyt modul.
Lad os nu skrive kode, således at kolonne B indeholder værdierne i kolonne D (navn) og kolonne E.
Syntaksen er angivet som:
For det første anvender en 'for' bue fra i = 4, idet værdierne ud fra 4 th række i dette tilfælde. Sløjfen fortsætter indtil slutningen af den sidste række i kolonne C. Så variablen I kan bruges som et række nummer inde i 'for' sløjfen.
Indtast derefter den værdi, der skal tildeles til celle (række nummer, kolonne B), som kan gives som celler (i, “B”). Værdi som celle (række nummer, kolonne D) & “_” & celle (række nummer, kolonne E ).
Antag at du vil tildele cellen B5 = D5 & “_” & E5, du kan simpelthen bruge koden som:
Celler (5, "B"). Værdi = Celler (5, "D"). Værdi & "_" & Celler (5, "E"). Værdi
Lad os nu kigge efter opslagsværdien i arrayet B5: E24. Du skal først indtaste opslagsværdien. Lad os tage værdien (navn og afdeling) fra brugeren. At gøre dette,
- definer tre variabler, navn, afdeling og opslagsværdi som en streng.
- Tag navnet input fra brugeren. Brug koden:
navn = InputBox ("Indtast navnet på medarbejderen")
Indholdet i indtastningsfeltet "Indtast …" vises i promptfeltet, når du kører koden. Den streng, der indtastes i prompten, tildeles navnevariablen.
- Tag afdelingen fra brugeren. Det kan gøres på samme måde som ovenfor.
afdeling = InputBox ("Indtast medarbejderens afdeling")
- Tildel navnet og afdelingen med "_" som en separator til variablen lookup_val ved hjælp af følgende syntaks:
lookup_val = navn & “_” & afdeling
- Skriv vlookup-syntaksen for at søge efter lookup_val i interval B5: E24 returnerer den i en variabel løn.
Initialiser den variable løn:
Dim løn så længe
Brug Vlookup-funktionen til at finde lookup_val. Table_array kan angives som Range (“B: F”), og lønnen findes i den femte kolonne. Følgende syntaks kan således bruges:
løn = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- For at udskrive lønnen i en meddelelsesboks skal du bruge syntaksen:
MsgBox (medarbejderens løn er ”& løn)
Kør nu koden. En promptboks vises i regnearket, hvor du kan indtaste navnet. Når du har indtastet navnet (Say Sashi) og klik på OK.
Det åbner et andet felt, hvor du kan komme ind i afdelingen. Når du er kommet ind i afdelingen, skal du sige IT.
Det udskriver medarbejderens løn.
Hvis Vlookup kan finde en medarbejder med navn og afdeling, vil det give en fejl. Antag at du giver navnet “Vishnu” og afdeling “IT”, det returnerer Kørselstidsfejl '1004'.
For at løse dette problem kan du angive i koden, at der på denne type fejl skal udskrives "Værdi ikke fundet" i stedet. At gøre dette,
- Inden du bruger vlookup-syntaksen, skal du bruge følgende kode-
Ved fejl GoTo-meddelelse
Kontrollere:
Den efterfølgende kode (af Check :) overvåges, og hvis den modtager en fejl, går den til "meddelelses" -erklæringen
- I slutningen af koden (Before End Sub) skal du angive, at hvis fejlnummeret er 1004, skal du udskrive i meddelelsesfeltet "Medarbejderdata ikke til stede." Dette kan gøres ved hjælp af syntaksen:
Besked:
Hvis Err.Number = 1004 Så
MsgBox (“Medarbejderdata ikke til stede”)
Afslut Hvis
Modul 1:
Sub vlookup3 ()For i = 4 Til celler (Rækker.Tælling, "C"). Afslut (xlUp). Rødceller
(i, "B"). Værdi = Celler (i, "D"). Værdi & "_ ”& Celler (i,“ E ”). Værdi
Næste iDim-navn Som streng
Dim-afdeling Som streng
Dim lookup_val Som streng
Dim-løn Som Longname = InputBox (“ Indtast medarbejderens navn ”)
afdeling = InputBox (“ Indtast afdeling for medarbejderen ”)
lookup_val = navn &“ _ ”& departmentOn Error GoTo Meddelelseskontrol
:
løn = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, Falsk)
MsgBox (“ Medarbejderens løn er ”& Løn) Besked:
Hvis Err.Number = 1004 Så
MsgBox (“ Medarbejderdata ikke til stede ”)
Afslut IfEnd Sub
Ting at huske på VLookup i Excel VBA
- Vlookup-funktionen kan kaldes i Excel VBA ved hjælp af WorksheetFunction.
- Syntaksen for vlookup-funktionen forbliver den samme i Excel VBA.
- Når VBA vlookup-koden ikke kan finde lookup_value, vil det give en 1004-fejl.
- Fejlen i vlookup-funktionen kan styres ved hjælp af en goto-sætning, hvis den returnerer en fejl.
