Vlookup je funkcija delovnega lista v Excelu, vendar jo je mogoče uporabiti tudi v VBA, funkcionalnost Vlookupa je podobna funkcionalnosti VBA in na delovnem listu, saj je funkcija za uporabo Vlookupa v VBA v aplikaciji Delovna lista. metoda in argumenti ostajajo enaki.
Funkcija VLOOKUP v Excelu VBA
Funkcija VLOOKUP v Excelu se uporablja za iskanje vrednosti v polju in vrnitev ustrezne vrednosti iz drugega stolpca. Vrednost za iskanje mora biti prisotna v prvem stolpcu. Omeniti je treba tudi, ali je treba iskati natančno ali približno. Funkcijo delovnega lista VLOOKUP lahko uporabite pri kodiranju VBA. Funkcija ni vgrajena VBA in zato lahko kliče samo z delovnim listom.
Funkcija VLOOKUP v Excelu ima naslednjo sintakso:
V kateri je lookup_value vrednost, ki jo je treba iskati, table_arrray je tabela, col_index_num je številka stolpca vrnjene vrednosti, range_lookup pomeni, ali je ujemanje natančno ali približno. range_lookup je lahko TRUE / FALSE ali 0/1.
V kodi VBA lahko funkcijo VLOOKUP uporabljamo kot:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Kako uporabiti VLookup v Excelu VBA?
Spodaj je nekaj primerov kode VLookup v Excelu VBA.
Koda VLookup v Excelu VBA Primer # 1
Poglejmo, kako lahko v Excelu VBA pokličemo funkcijo delovnega lista VLOOKUP.
Recimo, da so vam na voljo osebni podatki, ime in povprečne ocene študentov.
Zdaj želite poiskati ocene, ki jih je pridobil študent z ID 11004.
Če želite poiskati vrednost, sledite naslednjim korakom:
- Pojdite na zavihek za razvijalce in kliknite Visual Basic.
- V oknu VBA pojdite na Vstavi in kliknite Modul.
- Zdaj napišite kodo VBA VLOOKUP. Uporabite lahko naslednjo kodo VBA VLOOKUP.
Sub vlookup1 ()
Dim student_id Dokler
Dim znamke Dokler
student_id = 11004
Set myrange = območje ( "B4: D8")
znamke = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Najprej določite ID študenta, ki je vrednost za iskanje. Zato določimo,
študentski_id = 11004
Nato določimo obseg, v katerem obstaja vrednost in vrnjena vrednost. Ker so naši podatki prisotni v celicah B4: D8, določimo obseg - razpon:
Nastavi moj obseg = obseg (“B4: D8”)
Na koncu vnesemo funkcijo VLOOKUP s pomočjo funkcije Worksheet v spremenljivko, označeno kot:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Za tiskanje oznak v okence za sporočila uporabimo naslednji ukaz:
MsgBox »Študent z ID:« & student_id & »pridobljen« & ocene & »oznake«
Vrnilo se bo:
Študent z ID: 11004 je pridobil 85 mark.
Zdaj kliknite gumb za zagon.
Opazili boste, da se na Excelovem listu prikaže sporočilo.
Koda VLookup v Excelu VBA Primer # 2
Recimo, da imate podatke o imenih zaposlenih in njihovi plači. Ti podatki so podani v stolpca B in C. Zdaj morate napisati kodo VBA VLOOKUP, tako da bo glede na ime zaposlenega v celici, F4, plača zaposlenega vrnjena v celico G4.
Zapišimo kodo VBA VLOOKUP.
- Določite obseg, v katerem so vrednosti, tj. Stolpca B in C.
Nastavite moj obseg = obseg (“B: C”)
- Določite ime zaposlenega in vnesite ime iz celice F4.
Ime nastavitve = obseg (“F4”)
- Določite plačo kot celico G4.
Nastavite plačo = obseg (“G4”)
- Zdaj pokličite funkcijo VLOOKUP s pomočjo WorksheetFunction v VBA in jo vnesite v plačo. To bo vrnilo vrednost (izhod funkcije Vlookup) v celici G4. Uporabi se lahko naslednja skladnja:
pay.Value = Application.WorksheetFunction.VLookup (ime, moje območje, 2, napačno)
- Zdaj zaženite modul. Celica G4 bo vsebovala plačo zaposlenega po zagonu kode VBA VLOOKUP.
Recimo, da spremenite vrednost celice F4 na »David« na delovnem listu in znova zaženete kodo, in vrnila bo Davidovo plačo.
Koda VLookup v Excelu VBA Primer # 3
Recimo, da imate podatke o zaposlenem v vašem podjetju z osebnimi podatki, imeni, oddelki in plačo. Z uporabo Vlookupa v VBA želite dobiti podatke o plači zaposlenega z uporabo njegovega imena in oddelka.
Ker funkcija vlookup v Excelu išče lookup_value v samo enem stolpcu, ki je prvi stolpec polja_tabela, morate najprej narediti stolpec, ki vsebuje "Ime" in "Oddelek" vsakega zaposlenega.
V VBA vstavimo vrednosti »Ime« in »Oddelek« v stolpec B delovnega lista.
Če želite to narediti, pojdite na zavihek Razvijalec in kliknite Visual Basic. Nato pojdite na Vstavi in kliknite Modul, da začnete nov modul.
Zapišimo zdaj kodo, tako da stolpec B vsebuje vrednosti stolpca D (ime) in stolpca E.
Sintaksa je podana kot:
Prvič, uporaba a "za" zanke od i = 4, ker so vrednosti so izhajajoč iz 4 th vrstico v tem primeru. Zanka se bo nadaljevala do konca zadnje vrstice stolpca C. Torej, spremenljivko I lahko uporabimo kot številko vrstice znotraj zanke 'for'.
Nato vnesite vrednost, ki jo želite dodeliti Celici (številka_ vrstice, stolpec B), ki jo lahko podate kot Celice (i, »B«). ).
Recimo, da želite celici dodeliti B5 = D5 & "_" & E5, lahko preprosto uporabite kodo kot:
Celice (5, “B”). Vrednost = Celice (5, “D”). Vrednost & “_” & Celice (5, “E”). Vrednost
Zdaj pa poiščimo vrednost iskanja v polju B5: E24. Najprej morate vnesti iskalno vrednost. Uporabniku vzemimo vrednost (Ime in oddelek). Storiti to,
- definirajte tri spremenljivke, ime, oddelek in iskalni_val kot niz.
- Vnesite ime od uporabnika. Uporabite kodo:
name = InputBox ("Vnesite ime zaposlenega")
Vsebina v polju za vnos »Enter the…« se bo prikazala v pozivu, ko zaženete kodo. Niz, vnesen v poziv, bo dodeljen spremenljivki imena.
- Vzemite oddelek od uporabnika. To lahko storite podobno kot zgoraj.
department = InputBox ("Vnesite oddelek zaposlenega")
- Spremenljivki lookup_val dodelite ime in oddelek z »_« kot ločilo z naslednjo sintakso:
lookup_val = ime & "_" & oddelek
- Napišite sintakso vlookup za iskanje lookup_val v obsegu B5: E24 ga vrne s spremenljivo plačo.
Inicializirajte spremenljivo plačo:
Dim plača tako dolgo
S funkcijo Vlookup poiščite lookup_val. Table_array se lahko daje kot območje ( "B: F"), in plača je prisoten v 5 th stolpec. Tako lahko uporabimo naslednjo sintakso:
plača = Application.WorksheetFunction.VLookup (lookup_val, obseg (“B: F”), 5, napačno)
- Če želite plačo natisniti v okno za sporočila, uporabite sintakso:
MsgBox (Plača zaposlenega je "& plača)
Zdaj zaženite kodo. Na delovnem listu se prikaže pozivno polje, v katerega lahko vnesete ime. Ko vnesete ime (recimo Sashi) in kliknete V redu.
Odprlo se bo drugo polje, v katerem lahko vstopite v oddelek. Ko vstopite na oddelek, recite IT.
Natisnil bo plačo zaposlenega.
Če lahko Vlookup najde katerega koli zaposlenega z imenom in oddelkom, se bo pojavila napaka. Recimo, da navedete ime "Vishnu" in oddelek "IT", to bo vrnilo napako med izvajanjem '1004'.
Če želite odpraviti to težavo, lahko v kodi določite, da pri tej vrsti napake natisnete »Vrednost ni najdena«. Storiti to,
- Pred uporabo sintakse vlookup uporabite naslednjo kodo -
Ob napaki GoTo Message
Preverite:
Nadzorna koda (Check Check :) bo nadzorovana in če bo prejela napako, bo preusmerjena na stavek "message"
- Na koncu kode (Before End Sub) določite, da če je številka napake 1004, natisnite v polje za sporočilo »Podatki o zaposlenih niso prisotni«. To lahko storite s pomočjo sintakse:
Sporočilo:
Če je Err.Number = 1004, potem
MsgBox (»Podatki o zaposlenih niso na voljo«)
Končaj če
1. modul:
Sub vlookup3 ()Za i = 4 do celic (vrstice. Število, "C"). Konec (xlUp). Vrstice
celic (i, "B"). Vrednost = celice (i, "D"). Vrednost & "_ "& Celice (i," E "). Vrednost
Naprej iDim ime Kot niz
Dim oddelek Kot niz
Dim lookup_val Kot niz
Dim plača Kot Longname = InputBox (" Vnesite ime zaposlenega ")
oddelek = InputBox (" Vnesite oddelek zaposleni ")
lookup_val = ime &" _ "& departmentOn Error GoTo
Preverjanje sporočil :
plača = Application.WorksheetFunction.VLookup (lookup_val, Obseg (" B: F "), 5, False)
MsgBox (" Plača zaposlenega je ”& Plača) Sporočilo:
Če je Err.Number = 1004 Potem
MsgBox („ Podatki o zaposlenih niso na voljo “)
End IfEnd Sub
Stvari, ki si jih je treba zapomniti pri VLookup v Excelu VBA
- Funkcijo Vlookup lahko v Excelu VBA pokličete s pomočjo WorksheetFunction.
- Sintaksa funkcije vlookup ostaja enaka v Excelu VBA.
- Ko koda vlookup VBA ne najde lookup_value, bo povzročila napako 1004.
- Napako v funkciji vlookup lahko upravljate s pomočjo stavka goto, če vrne napako.
