Vlookup er en regnearkfunksjon i excel, men den kan også brukes i VBA, funksjonaliteten til Vlookup ligner funksjonaliteten i VBA og i regnearket begge, da det er en regnearkfunksjon, er metoden for å bruke Vlookup i VBA gjennom Application.WorksheetFunction metoden og argumentene forblir de samme.
VLOOKUP Funksjon i Excel VBA
VLOOKUP-funksjonen i Excel brukes til å søke etter en verdi i en matrise og returnere den tilsvarende verdien fra en annen kolonne. Verdien som skal søkes, skal være til stede i den første kolonnen. Det er også nødvendig å nevne om du skal lete etter en eksakt samsvar eller en omtrentlig kamp. Regnearkfunksjonen VLOOKUP kan brukes i VBA-koding. Funksjonen er ikke innebygd VBA og kan derfor bare ringe ved hjelp av regnearket.
VLOOKUP-funksjonen i Excel har følgende syntaks:
I hvilken lookup_value er verdien du skal se etter, table_arrray er tabellen, col_index_num er kolonnenummeret til returverdien, range_lookup betyr om samsvaret er nøyaktig eller omtrentlig. range_lookup kan være TRUE / FALSE eller 0/1.
I VBA-kode kan VLOOKUP-funksjonen brukes som:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Hvordan bruker jeg VLookup i Excel VBA?
Nedenfor er noen eksempler på VLookup-koden i Excel VBA.
VLookup Code i Excel VBA Eksempel nr. 1
La oss se hvordan vi kan kalle regnearkfunksjonen VLOOKUP i Excel VBA.
Anta at du har data om studenters ID, navn og gjennomsnittlige karakterer oppnådd av dem.
Nå vil du slå opp karakterene som er oppnådd av en student med ID 11004.
Følg disse trinnene for å slå opp verdien:
- Gå til kategorien Developer og klikk på Visual Basic.
- Under VBA-vinduet, gå til Sett inn og klikk på Modul.
- Skriv nå VBA VLOOKUP-koden. Følgende VBA VLOOKUP-kode kan brukes.
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 definerer du en student-ID, som er verdien du skal slå opp. Derfor definerer vi,
student_id = 11004
Deretter definerer vi området der verdien og returverdien eksisterer. Siden dataene våre er tilstede i cellene B4: D8, definerer vi en rekkevidde som:
Set myrange = Range (“B4: D8”)
Til slutt legger vi inn VLOOKUP-funksjonen ved hjelp av regnearkfunksjonen i en variabel, merker som:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
For å skrive ut merkene i en meldingsboks, la oss bruke følgende kommando:
MsgBox “Student med ID:” & student_id & ”oppnådd” & merker & ”merker”
Det kommer tilbake:
En student med ID: 11004 fikk 85 karakterer.
Klikk nå på Run-knappen.
Du vil merke at en meldingsboks vises i Excel-arket.
VLookup Code i Excel VBA Eksempel nr. 2
Anta at du har dataene om navnene på de ansatte og lønnen deres. Disse dataene er gitt kolonnene B og C. Nå må du skrive en VBA VLOOKUP-kode slik at gitt navnet på den ansatte i en celle, F4, vil lønnen til den ansatte bli returnert i cellen G4.
La oss skrive VBA VLOOKUP-koden.
- Definer området der verdiene er tilstede, dvs. kolonner B og C.
Sett myrange = Range (“B: C”)
- Definer navnet på den ansatte og skriv inn navnet fra cellen F4.
Set name = Range (“F4”)
- Definer lønn som celle G4.
Angi lønn = Range (“G4”)
- Nå kan du ringe til VLOOKUP-funksjonen ved hjelp av WorksheetFunction i VBA og legge den inn i lønn. Dette vil returnere verdien (utdata fra Vlookup-funksjonen) i cellen G4. Følgende syntaks kan brukes:
lønn.Value = Application.WorksheetFunction.VLookup (name, myrange, 2, False)
- Kjør nå modulen. Cellen G4 inneholder lønnen til den ansatte etter at du har kjørt VBA VLOOKUP-koden.
Anta at du endrer verdien på celle F4 til "David" i regnearket og kjører koden på nytt, og den vil returnere lønnen til David.
VLookup Code i Excel VBA Eksempel # 3
Anta at du har dataene til den ansatte i selskapet ditt, med ID, navn, avdeling og lønn. Ved å bruke Vlookup i VBA, vil du få lønnsopplysningene til en ansatt ved hjelp av hans navn og avdeling.
Siden vlookup-funksjonen i excel søker i oppslagsverdien i bare en enkelt kolonne, som er den første kolonnen i tabellarrayen, er det nødvendig at du først lager en kolonne som inneholder "Navn" og "Avdeling" for hver ansatt.
La oss i VBA sette inn verdiene “Navn” og “Avdeling” i kolonne B i regnearket.
For å gjøre dette, gå til kategorien Utvikler og klikk på Visual Basic. Gå deretter til Sett inn og klikk på Modul for å starte en ny modul.
La oss nå skrive kode slik at kolonne B inneholder verdiene til kolonne D (navn) og kolonne E.
Syntaksen er gitt som:
For det første bruker en 'for' sløyfe fra i = 4 siden de verdier som starter fra 4- th rad i dette tilfellet. Sløyfen fortsetter til slutten av den siste raden i kolonne C. Så, variabelen I kan brukes som et radnummer i 'for' sløyfen.
Skriv deretter inn verdien du vil tildele for Cell (radnummer, kolonne B), som kan gis som celler (i, “B”). Verdi, som Cell (radnummer, kolonne D) & “_” & Cell (radnummer, kolonne E ).
Anta at du vil tildele cellen B5 = D5 & “_” & E5, du kan ganske enkelt bruke koden som:
Celler (5, "B"). Verdi = Celler (5, "D"). Verdi & "_" & Celler (5, "E"). Verdi
La oss nå se etter oppslagsverdien i matrisen B5: E24. Du må først oppgi oppslagsverdien. La oss ta verdien (navn og avdeling) fra brukeren. Å gjøre dette,
- definere tre variabler, navn, avdeling og oppslag_val som en streng.
- Ta navnet fra brukeren. Bruk koden:
navn = InputBox ("Skriv inn navnet på den ansatte")
Innholdet i inntastingsboksen "Skriv inn …" vil vises i ledeteksten når du kjører koden. Strengen som ble oppgitt i ledeteksten, vil bli tildelt navnevariabelen.
- Ta avdelingen fra brukeren. Det kan gjøres på samme måte som ovenfor.
avdeling = InputBox ("Gå inn i avdelingen til den ansatte")
- Tilordne navnet og avdelingen med “_” som skilletegn til variabelen lookup_val ved å bruke følgende syntaks:
lookup_val = navn & “_” og avdeling
- Skriv vlookup-syntaksen for å søke etter lookup_val i området B5: E24 returnerer den i en variabel lønn.
Initialiser den variable lønnen:
Dunk lønn Så lenge
Bruk Vlookup-funksjonen til å finne lookup_val. Table_array kan gis som Range (“B: F”), og lønnen er tilstede i den femte kolonnen. Følgende syntaks kan således brukes:
lønn = Application.WorksheetFunction.VLookup (lookup_val, Range (“B: F”), 5, False)
- For å skrive ut lønnen i en meldingsboks, bruk syntaksen:
MsgBox (Lønnen til den ansatte er ”og lønn)
Kjør nå koden. En hurtigboks vises i regnearket der du kan skrive inn navnet. Etter at du har angitt navnet (Say Sashi) og klikk OK.
Det åpner en annen rute der du kan komme inn på avdelingen. Når du har kommet inn på avdelingen, si IT.
Det vil skrive ut lønnen til den ansatte.
Hvis Vlookup finner en ansatt med navn og avdeling, vil det gi en feil. Anta at du gir navnet "Vishnu" og avdelingen "IT", det vil returnere Kjøretidsfeil '1004'.
For å løse dette problemet kan du i koden angi at på denne typen feil skriver du ut "Verdi ikke funnet" i stedet. Å gjøre dette,
- Før du bruker vlookup-syntaksen, bruk følgende kode-
Ved feil GoTo-melding
Kryss av:
Den etterfølgende koden (av Sjekk :) vil bli overvåket, og hvis den får en feil, vil den gå til "meldings" -erklæringen
- På slutten av koden (Before End Sub), spesifiser at hvis feilnummeret er 1004, så skriv ut i meldingsboksen "Medarbeiderdata ikke til stede." Dette kan gjøres ved hjelp av syntaksen:
Beskjed:
Hvis feilnummer = 1004 da
MsgBox (“Data om ansatte ikke til stede”)
Slutt om
Modul 1:
Sub vlookup3 ()For i = 4 Til celler (Rader.Telling, "C"). End (xlUp). Radceller
(i, "B"). Verdi = Celler (i, "D"). Verdi & "_ ”& Cells (i,“ E ”). Verdi
Neste iDim-navn Som streng
Dim-avdeling Som streng
Dim lookup_val Som streng
Dim-lønn Som Longname = InputBox (“ Skriv inn navnet på den ansatte ”)
avdeling = InputBox (“ Angi avdelingen for den ansatte ”)
lookup_val = navn &“ _ ”& avdeling På feil Gå til meldingskontroll
:
lønn = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, False)
MsgBox (“ Lønnen til den ansatte er ”& Lønn) Melding:
Hvis feilnummer = 1004 så
MsgBox (“ Arbeidstakerdata ikke til stede ”)
End IfEnd Sub
Ting å huske på VLookup i Excel VBA
- Vlookup-funksjonen kan kalles i Excel VBA ved å bruke WorksheetFunction.
- Syntaksen til vlookup-funksjonen forblir den samme i Excel VBA.
- Når VBA vlookup-koden ikke finner lookup_value, vil det gi en 1004-feil.
- Feilen i vlookup-funksjonen kan håndteres ved hjelp av en goto-setning hvis den returnerer en feil.
