Vlookup je funkcija radnog lista u Excelu, ali može se koristiti i u VBA-u, funkcionalnost Vlookupa slična je funkcionalnosti VBA-a i na radnom listu, budući da je funkcija radnog lista metoda korištenja Vlookupa u VBA-u kroz Application.WorksheetFunction metoda i argumenti ostaju isti.
VLOOKUP funkcija u programu Excel VBA
Funkcija VLOOKUP u Excelu koristi se za pretraživanje vrijednosti u polju i vraćanje odgovarajuće vrijednosti iz drugog stupca. Vrijednost za pretraživanje trebala bi biti prisutna u prvom stupcu. Također je potrebno spomenuti treba li tražiti točno ili približno podudaranje. Funkcija radnog lista VLOOKUP može se koristiti u VBA kodiranju. Funkcija nije ugrađena u VBA i stoga može pozivati samo pomoću radnog lista.
Funkcija VLOOKUP u Excelu ima sljedeću sintaksu:
U kojoj je lookup_value vrijednost koju treba tražiti, table_arrray je tablica, col_index_num je broj stupca povratne vrijednosti, range_lookup označava je li podudaranje točno ili približno. range_lookup može biti TRUE / FALSE ili 0/1.
U VBA kodu funkcija VLOOKUP može se koristiti kao:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Kako koristiti VLookup u Excelu VBA?
Ispod su neki primjeri VLookup koda u programu Excel VBA.
VLookup kod u Excelu VBA Primjer # 1
Pogledajmo kako možemo nazvati funkciju radnog lista VLOOKUP u programu Excel VBA.
Pretpostavimo da imate podatke o osobnoj iskaznici, imenu i prosječnim ocjenama koje su oni dobili.
Sada želite potražiti ocjene koje je student stekao s ID-om 11004.
Da biste pronašli vrijednost, slijedite sljedeće korake:
- Idite na karticu Developer i kliknite Visual Basic.
- Ispod VBA prozora idite na Umetni i kliknite Modul.
- Sada napišite VBA VLOOKUP kôd. Sljedeći VBA VLOOKUP kôd se može koristiti.
Pod vlookup1 ()
Dim student_id Kao čeznuti
DIM maraka Kao čeznuti
student_id = 11004
Postavi myrange = Range ( „B4: D8”)
oznake = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Prvo definirajte studentski ID koji je vrijednost za traženje. Stoga definiramo,
student_id = 11004
Dalje, definiramo raspon u kojem vrijednost i povratna vrijednost postoje. Budući da su naši podaci prisutni u stanicama B4: D8, definiramo opseg - raspon kao:
Postavi moj opseg = Raspon (“B4: D8”)
Na kraju, unosimo funkciju VLOOKUP pomoću funkcije Radni list u varijablu, označava kao:
oznake = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Da bismo ispisali oznake u okviru s porukom, upotrijebimo sljedeću naredbu:
MsgBox "Student s ID-om:" & student_id & "dobiven" & ocjene & "ocjene"
Vratit će se:
Student sa ID: 11004 dobio je 85 maraka.
Sada kliknite gumb za pokretanje.
Primijetit ćete da će se na Excel listu pojaviti okvir s porukom.
VLookup kod u Excelu VBA Primjer # 2
Pretpostavimo da imate podatke o imenima zaposlenika i njihovoj plaći. Ovi podaci dobivaju stupce B i C. Sada morate napisati VBA VLOOKUP kôd takav da će se s obzirom na ime zaposlenika u ćeliji, F4, plaća zaposlenika vratiti u ćeliju G4.
Napišimo VBA VLOOKUP kôd.
- Definirajte raspon u kojem su vrijednosti prisutne, tj. Stupci B i C.
Postavi moj opseg = Raspon ("B: C")
- Definirajte ime zaposlenika i unesite ga iz ćelije F4.
Naziv skupa = Raspon (“F4”)
- Definirajte plaću kao ćeliju G4.
Postavi plaću = Raspon ("G4")
- Sada pozovite funkciju VLOOKUP koristeći WorksheetFunction u VBA i unesite je u pay.Value. To će vratiti vrijednost (izlaz funkcije Vlookup) u ćeliji G4. Može se koristiti sljedeća sintaksa:
pay.Value = Application.WorksheetFunction.VLookup (ime, moj raspon, 2, Netačno)
- Sada pokrenite modul. Ćelija G4 sadržavat će plaću zaposlenika nakon pokretanja VBA VLOOKUP koda.
Pretpostavimo da na radnom listu promijenite vrijednost ćelije F4 u "David" i ponovno pokrenete kôd, a vratit će Davidovu plaću.
VLookup kod u Excelu VBA Primjer # 3
Pretpostavimo da imate podatke zaposlenika u vašoj tvrtki, njihove osobne iskaznice, imena, odjele i plaću. Korištenjem Vlookupa u VBA-u želite dobiti podatke o plaći zaposlenika koristeći njegovo ime i odjel.
Budući da funkcija vlookup u excelu pretražuje vrijednost_traženja u samo jednom stupcu, što je prvi stupac niza table_, potrebno je da prvo napravite stupac koji sadrži "Ime" i "Odjel" svakog zaposlenika.
U VBA umetnimo vrijednosti "Ime" i "Odjel" u stupac B radnog lista.
Da biste to učinili, idite na karticu Developer i kliknite Visual Basic. Zatim idite na Umetni i kliknite Modul da biste pokrenuli novi modul.
Napišimo sada kod, takav da stupac B sadrži vrijednosti stupca D (ime) i stupca E.
Sintaksa je dana kao:
Prvo, koristiti „za” petlji od i = 4, jer su vrijednosti počevši od 4 -og red u tom slučaju. Petlja će se nastaviti do kraja posljednjeg retka stupca C. Dakle, varijabla I može se koristiti kao broj retka unutar petlje 'for'.
Zatim unesite vrijednost koju ćete dodijeliti ćeliji (broj_reda, stupac B), koja se može dati kao ćelije (i, "B"). Vrijednost, kao ćelija (broj_reda, stupac D) & "_" & ćelija (broj_reda, stupac E ).
Pretpostavimo da želite dodijeliti ćeliji B5 = D5 & “_” & E5, možete jednostavno koristiti kôd kao:
Ćelije (5, "B"). Vrijednost = Ćelije (5, "D"). Vrijednost & "_" & Ćelije (5, "E"). Vrijednost
Sada, potražimo vrijednost pretraživanja u polju B5: E24. Prvo morate unijeti vrijednost pretraživanja. Uzmimo vrijednost (Ime i odjel) od korisnika. Uraditi ovo,
- definirajte tri varijable, ime, odjel i lookup_val kao niz.
- Unesite ime od korisnika. Koristite kod:
name = InputBox ("Unesite ime zaposlenika")
Sadržaj u okviru za unos "Enter the …" prikazat će se u okviru za upit kada pokrenete kôd. Niz upisan u prompt bit će dodijeljen varijabli imena.
- Uzmi odjel od korisnika. To se može učiniti slično kao gore.
odjel = InputBox ("Unesite odjel zaposlenika")
- Dodijelite ime i odjel s “_” kao separator varijabli lookup_val koristeći sljedeću sintaksu:
lookup_val = ime & “_” & odjel
- Napišite vlookup sintaksu za traženje lookup_val u rasponu B5: E24 ga vraća u varijabilnoj plaći.
Inicijalizirajte varijabilnu plaću:
Slaba plaća dokle god
Upotrijebite funkciju Vlookup za pronalaženje lookup_val. Table_array može dati izboru ( „B: F”) i plaća prisutan na 5 -om stupcu. Tako se može koristiti sljedeća sintaksa:
plaća = Application.WorksheetFunction.VLookup (lookup_val, Raspon (“B: F”), 5, Netačno)
- Da biste ispisali plaću u okvir za poruke, upotrijebite sintaksu:
MsgBox (Plaća zaposlenika je "& plaća)
Sada pokrenite kod. Na radnom listu pojavit će se okvir za upit u koji možete unijeti ime. Nakon što unesete ime (Recite Sashi) i kliknite U redu.
Otvorit će se još jedan okvir u kojem možete ući u odjel. Nakon što uđete na odjel, recite IT.
Ispisat će plaću zaposlenika.
Ako Vlookup pronađe bilo kojeg zaposlenika s imenom i odjelom, ispisat će pogrešku. Pretpostavimo da date ime "Vishnu" i odjel "IT", vratit će se pogreška u vremenu izvođenja '1004'.
Da biste riješili ovaj problem, u kodu možete navesti da na ovoj vrsti pogreške umjesto toga ispišite "Vrijednost nije pronađena". Uraditi ovo,
- Prije upotrebe vlookup sintakse, upotrijebite sljedeći kod-
O pogrešci GoTo Message
Ček:
Pratit će se prateći kôd (Check Check :), a ako dobije pogrešku, preći će na izjavu "poruka"
- Na kraju koda (Before End Sub), navedite da ako je broj pogreške 1004, ispišite u okvir za poruku "Podaci zaposlenika nisu prisutni." To se može učiniti pomoću sintakse:
Poruka:
Ako je Err.Broj = 1004 Tada
MsgBox („Podaci o zaposleniku nisu prisutni“)
Završi ako
Modul 1:
Sub vlookup3 ()Za i = 4 do ćelija (retci.broj, “C”). Kraj (xlUp) .redne
ćelije (i, “B”). Vrijednost = ćelije (i, “D”). Vrijednost & “_ "& Cells (i," E "). Vrijednost
Sljedeće iDim ime Kao niz
Dim odjel Kao niz
Dim lookup_val Kao niz
Dim plaću Kao Longname = InputBox (" Unesite ime zaposlenika ")
odjel = InputBox (" Unesite odjel zaposlenik ")
lookup_val = name &" _ "& departmentOn Error GoTo
Provjera poruke :
pay = Application.WorksheetFunction.VLookup (lookup_val, Range (" B: F "), 5, False)
MsgBox (" Plaća zaposlenika je ”I plaća) Poruka:
Ako je Err.Number = 1004, onda
MsgBox („ Podaci o zaposleniku nisu prisutni “)
Kraj IfEnd Sub
Stvari koje treba zapamtiti o VLookup-u u programu Excel VBA
- Funkcija Vlookup može se pozvati u programu Excel VBA pomoću WorksheetFunction.
- Sintaksa funkcije vlookup ostaje ista u programu Excel VBA.
- Kada VBA vlookup kôd ne može pronaći lookup_value, dat će pogrešku 1004.
- Pogreškom u funkciji vlookup može se upravljati pomoću izraza goto ako vrati pogrešku.
