Vlookup on Exceli töölehe funktsioon, kuid seda saab kasutada ka VBA-s, Vlookupi funktsionaalsus on sarnane nii VBA kui ka töölehe funktsionaalsusega, kuna see on töölehe funktsioon, meetod Vlookupi kasutamiseks VBA-s toimub rakenduse kaudu. meetod ja argumendid jäävad samaks.
Funktsioon VLOOKUP Exceli VBA-s
Exceli funktsiooni VLOOKUP kasutatakse massiivi väärtuse otsimiseks ja selle vastava väärtuse tagastamiseks teisest veerust. Otsitava väärtus peaks olema esimeses veerus. Samuti on vaja mainida, kas otsida täpset vastet või ligikaudset vastet. Töölehe funktsiooni VLOOKUP saab kasutada VBA kodeerimisel. Funktsioon pole sisseehitatud VBA ja seega saab seda helistada ainult töölehe abil.
Exceli funktsioonil VLOOKUP on järgmine süntaks:
Milles otsingu väärtus on otsitav väärtus, table_arrray on tabel, col_index_num on tagastatava väärtuse veeru number, vahemiku_lookup tähistab, kui vaste on täpne või ligikaudne. vahemiku_otsingu väärtus võib olla TÕENE / VALE või 0/1.
VBA-koodis saab funktsiooni VLOOKUP kasutada järgmiselt:
Application.WorksheetFunction.vlookup (otsingu_väärtus, tabeli_värv, col_index_num, vahemiku_otsing)
Kuidas kasutada VLookupi Exceli VBA-s?
Allpool on mõned näited VLookupi koodist Exceli VBA-s.
VLookupi kood Excelis VBA näide nr 1
Vaatame, kuidas saame Exceli VBA-s töölehe funktsiooni VLOOKUP kutsuda.
Oletame, et ta esitab õpilaste isikutunnistuse, nime ja nende poolt saadud keskmised hinded.
Nüüd soovite üles otsida õpilase, kelle ID on 11004, saadud hinded.
Väärtuse otsimiseks toimige järgmiselt.
- Minge vahekaardile Arendaja ja klõpsake nuppu Visual Basic.
- VBA akna all minge jaotisse Lisa ja klõpsake nuppu Moodul.
- Nüüd kirjutage VBA VLOOKUP kood. Kasutada saab järgmist VBA VLOOKUP koodi.
Subvlookup1 () Hämardab õpilase_id
nii pika dimensiooniga kui pikk
Pikk õpilase_id
= 11004
Määra vahemiku = Vahemik („B4: D8”)
tähised = Application.WorksheetFunction.VLookup (õpilase ID, myrange, 3, vale)
Lõpp
Esiteks määrake õpilase ID, mis on otsitava väärtus. Seetõttu määratleme
student_id = 11004
Järgmisena määratleme vahemiku, milles väärtus ja tagastatav väärtus eksisteerivad. Kuna meie andmed on lahtrites B4: D8, määratleme vahemiku-vahemiku järgmiselt:
Määra vahemik = Vahemik ("B4: D8")
Lõpuks sisestame funktsiooni VLOOKUP, kasutades muutuja funktsiooni Tööleht, tähistatakse järgmiselt:
märgid = Application.WorksheetFunction.VLookup (õpilase_id, myrange, 3, False)
Sõnumikasti märkide printimiseks kasutage järgmist käsku:
MsgBox “Õpilane, kellel on ID:” & student_id & ”saadud” & marki & ”märki”
See naaseb:
Õpilane, kellel on ID: 11004, sai 85 märki.
Nüüd klõpsake nuppu Käivita.
Märkate, et Exceli lehele ilmub teatekast.
VLookupi kood Exceli VBA näites nr 2
Oletame, et teil on andmed töötajate nimede ja nende palga kohta. Nendele andmetele antakse veerud B ja C. Nüüd peate kirjutama VBA VLOOKUP-koodi nii, et antud lahtris töötaja nimi F4 tagastatakse töötaja palk lahtrisse G4.
Kirjutagem VBA VLOOKUP kood.
- Määratlege vahemik, milles väärtused esinevad, st veerud B ja C.
Määra vahemik = Vahemik ("B: C")
- Määratlege töötaja nimi ja sisestage nimi lahtrist F4.
Set name = Range („F4”)
- Määratlege palk lahtrina G4.
Määra palk = vahemik (“G4”)
- Nüüd helistage funktsioon VLOOKUP, kasutades VBA-s WorksheetFunction ja sisestage see palka. Väärtus. See tagastab lahtris G4 väärtuse (funktsiooni Vlookup väljund). Kasutada saab järgmist süntaksit:
palk.Väärtus = Application.WorksheetFunction.VLookup (nimi, vahemik, 2, vale)
- Nüüd käivitage moodul. Lahter G4 sisaldab töötaja palka pärast VBA VLOOKUP-koodi käivitamist.
Oletame, et muudate töölehe lahtris F4 väärtuseks „David“ ja käivitate koodi uuesti ning see tagastab Taaveti palga.
VLookupi kood Excelis VBA näide # 3
Oletame, et teil on oma ettevõtte töötaja andmed, kellel on tema ID, nimed, osakond ja palk. Kasutades VBA-s Vlookupi, soovite saada töötaja palgateavet tema nime ja osakonda kasutades.
Kuna Exceli funktsioon vlookup otsib otsingu_väärtust ainult ühest veerust, mis on tabeli_vajutus esimene veerg, tuleb kõigepealt teha veerg, mis sisaldab iga töötaja nime ja osakonda.
Lisage VBA-s töölehe B veergu väärtused "Nimi" ja "Osakond".
Selleks minge vahekaardile Arendaja ja klõpsake nuppu Visual Basic. Seejärel minge uue mooduli alustamiseks jaotisele Lisa ja klõpsake nuppu Moodul.
Kirjutagem nüüd kood nii, et veerg B sisaldab veeru D (nimi) ja veeru E väärtusi.
Süntaks on esitatud järgmiselt:
Esiteks, kasuta "poolt" kliendiliine i = 4 kuna väärtused alates 4 th reas antud juhul. Tsükkel jätkub veeru C viimase rea lõpuni. Muutujat I saab kasutada rea numbrina silmus „for”.
Seejärel sisestage lahtrile määratav väärtus (rea_number, veerg B), mille saab anda lahtritena (i, „B”). Väärtus lahtrina (rea_number, veerg D) & „_” & lahter (rea_number, veerg E ).
Oletame, et soovite määrata lahtrile B5 = D5 & “_” & E5, saate koodi kasutada lihtsalt järgmiselt:
Lahtrid (5, "B"). Väärtus = Lahtrid (5, "D"). Väärtus & "_" & Lahtrid (5, "E"). Väärtus
Nüüd laske otsida väärtust massiivist B5: E24. Kõigepealt peate sisestama otsingu väärtuse. Võtame kasutajalt väärtuse (nimi ja osakond). Selleks
- defineerige stringina kolm muutujat, nimi, osakond ja otsingu_val.
- Võtke kasutajalt sisestatud nimi. Kasutage koodi:
nimi = InputBox („Sisestage töötaja nimi”)
Sisestuskasti „Enter the…” sisu kuvatakse koodi käivitamisel viipakastis. Viipasse sisestatud string määratakse nimemuutujale.
- Võtke osakond kasutajalt. Seda saab teha sarnaselt ülaltoodule.
osakond = InputBox („Sisestage töötaja osakond“)
- Määrake nimi ja osakond muutujaga lookup_val eraldajana järgmise süntaksiga:
lookup_val = nimi & “_” & osakond
- Kirjutage vlookupi süntaks, et otsida vahemikku B5 otsingu_val: E24 tagastab selle muutuva palgana.
Muutke palga muutmine:
Hämar palk sama pikk
Otsingu_val leidmiseks kasutage funktsiooni Vlookup. Table_array võib manustada Range ( "B: F") ja palk viibib 5 th kolonni. Seega saab kasutada järgmist süntaksit:
palk = Application.WorksheetFunction.VLookup (otsingu_val, vahemik ("B: F"), 5, vale)
- Palga teatekasti printimiseks kasutage süntaksit:
MsgBox (Töötaja palk on & palk)
Nüüd käivitage kood. Töölehele ilmub viipekast, kuhu saate nime sisestada. Pärast nime sisestamist (Say Sashi) ja klõpsake nuppu OK.
See avab teise kasti, kus saate osakonda siseneda. Pärast osakonda sisenemist öelge IT.
See trükib töötaja palga.
Kui Vlookup suudab leida mõne töötaja, kellel on nimi ja osakond, annab see vea. Oletame, et annate nime „Višnu” ja osakond „IT”, see tagastab käitusaja vea 1004.
Selle probleemi lahendamiseks võite koodis täpsustada, et seda tüüpi tõrke korral printige selle asemel väärtus “Väärtust ei leitud”. Selleks
- Enne vlookupi süntaksit kasutage järgmist koodi-
On GoTo Messagei tõrge
Kontrollima:
Jälgitavat koodi (kontrolli :) jälgitakse ja kui see saab vea, läheb see lause “sõnum”
- Koodi lõpus (Enne lõpu alam) määrake, et kui tõrke number on 1004, printige sõnumikasti „Töötajate andmeid pole”. Seda saab teha süntaksiga:
Sõnum:
Kui Vea number = 1004 Siis
MsgBox („Töötajate andmeid pole”)
Lõpeta, kui
1. moodul:
Alamvlookup3 ()jaoks i = 4 rakkudele (Read.Count, "C"). Lõpp (xlUp). Rea
lahtrid (i, "B"). Väärtus = Cells (i, "D"). ”& Lahtrid (i,“ E ”). Väärtus
Järgmine iDim nimi String
Dim osakond As String
Dim lookup_val As String
Dim palk As Longname = InputBox (“ Sisesta töötaja nimi ”)
osakond = InputBox (“ Enter osakond töötaja ”)
lookup_val = nimi &“ _ ”& osakond Veaga GoTo sõnumi
kontroll:
palk = Application.WorksheetFunction.VLookup (lookup_val, Range (“ B: F ”), 5, False)
MsgBox (“ Töötaja palk on ”& Palk) Sõnum:
kui Vea number = 1004, siis
MsgBox („ Töötaja andmeid pole ”)
Lõpeta IfEnd alam
Mida tuleks Exceli VBA VLookupi kohta meeles pidada
- Funktsiooni Vlookup saab Exceli VBA-s kutsuda, kasutades funktsiooni WorksheetFunction.
- Funktsiooni vlookup süntaks jääb Exceli VBA-s samaks.
- Kui VBA vlookup kood ei leia otsinguväärtust, annab see vea 1004.
- Funktsiooni vlookup viga saab hallata goto-lause abil, kui see annab vea.
