Vlookup je funkcia listu v programe Excel, ale dá sa použiť aj vo VBA. Funkcia Vlookup je podobná funkcii vo VBA aj v hárku, pretože ide o funkciu listu. Metóda použitia Vlookup vo VBA je cez Application.WorksheetFunction metóda a argumenty zostávajú rovnaké.
Funkcia VLOOKUP v programe Excel VBA
Funkcia VLOOKUP v programe Excel sa používa na vyhľadanie hodnoty v poli a vrátenie zodpovedajúcej hodnoty z iného stĺpca. Hodnota, ktorá sa má vyhľadať, by mala byť v prvom stĺpci. Je tiež potrebné spomenúť, či sa má hľadať presná alebo približná zhoda. Pri kódovaní VBA je možné použiť funkciu listu VLOOKUP. Táto funkcia nie je zabudovaná do VBA, a preto je možné volať iba pomocou hárka.
Funkcia VLOOKUP v programe Excel má nasledujúcu syntax:
V ktorom je hľadaná hodnota hľadaná hodnota, tabuľková tabuľka, tabuľková tabuľka, col_index_num je číslo stĺpca návratovej hodnoty, range_lookup znamená, či je zhoda presná alebo približná. range_lookup môže byť TRUE / FALSE alebo 0/1.
V kóde VBA možno funkciu VLOOKUP použiť ako:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Ako používať VLookup v programe Excel VBA?
Ďalej uvádzame niekoľko príkladov kódu VLookup v programe Excel VBA.
Kód VLookup v príklade 1 aplikácie Excel VBA
Pozrime sa, ako môžeme nazvať funkciu listu VLOOKUP v programe Excel VBA.
Predpokladajme, že obsahuje údaje o ID, mene a priemerných známkach, ktoré študenti získali.
Teraz chcete vyhľadať známky získané študentom s ID 11004.
Pri vyhľadávaní hodnoty postupujte podľa nasledujúcich krokov:
- Prejdite na kartu Vývojár a kliknite na položku Visual Basic.
- V okne VBA choďte na Vložiť a kliknite na Modul.
- Teraz napíšte kód VBA VLOOKUP. Môže sa použiť nasledujúci kód VBA VLOOKUP VBA.
Sub vlookup1 ()
Dim student_id As Long
Dim brands As Long
student_id = 11004
Set myrange = Range (“B4: D8”)
brands = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Najskôr definujte ID študenta, čo je hodnota, ktorá sa má vyhľadať. Preto definujeme,
student_id = 11004
Ďalej definujeme rozsah, v ktorom existuje hodnota a návratová hodnota. Pretože naše údaje sú prítomné v bunkách B4: D8, definujeme rozsah-myrange ako:
Nastaviť myrange = rozsah („B4: D8“)
Nakoniec zadáme funkciu VLOOKUP pomocou funkcie List do premennej, ktorá bude označená ako:
známky = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Ak chcete vytlačiť značky v okne správy, použite nasledujúci príkaz:
MsgBox „Študent s ID:“ & student_id & „získané“ & známky & „známky“
Vráti sa:
Študent s ID: 11004 získal 85 známok.
Teraz kliknite na tlačidlo spustenia.
Všimnite si, že v hárku programu Excel sa zobrazí okno so správou.
Kód VLookup v príklade 2 aplikácie Excel VBA
Predpokladajme, že máte údaje o menách zamestnancov a ich plate. Tieto údaje sú uvedené v stĺpcoch B a C. Teraz musíte napísať kód VBA VLOOKUP VBA tak, aby pri zadaní mena zamestnanca v bunke F4 bol plat zamestnanca vrátený v bunke G4.
Napíšme kód VBA VLOOKUP.
- Definujte rozsah, v ktorom sú hodnoty prítomné, tj. Stĺpce B a C.
Nastaviť myrange = rozsah („B: C“)
- Definujte meno zamestnanca a zadajte meno z bunky F4.
Názov sady = Rozsah („F4“)
- Definujte plat ako bunku G4.
Nastavený plat = rozsah („G4“)
- Teraz zavolajte funkciu VLOOKUP pomocou WorksheetFunction vo VBA a zadajte ju v plate.Value. Týmto sa vráti hodnota (výstup funkcie Vlookup) v bunke G4. Môžete použiť nasledujúcu syntax:
plate.Value = Application.WorksheetFunction.VLookup (name, myrange, 2, False)
- Teraz spustite modul. Bunka G4 bude obsahovať plat zamestnanca po spustení kódu VBA VLOOKUP.
Predpokladajme, že v pracovnom hárku zmeníte hodnotu bunky F4 na „David“ a znovu spustíte kód, ktorý vráti Davidov plat.
Kód VLookup v programe Excel VBA, príklad č. 3
Predpokladajme, že máte k dispozícii údaje o zamestnancovi vašej spoločnosti vrátane ich ID, mien, oddelenia a platu. Pomocou Vlookup vo VBA chcete získať platové údaje zamestnanca pomocou jeho mena a oddelenia.
Pretože funkcia vlookup v programe Excel prehľadáva hodnotu_vyhľadávania iba v jednom stĺpci, ktorý je prvým stĺpcom tabuľky_array, je potrebné najskôr vytvoriť stĺpec obsahujúci „Meno“ a „Oddelenie“ každého zamestnanca.
V jazyku VBA vložíme do stĺpca B tabuľky hodnoty „Názov“ a „Oddelenie“.
Ak to chcete urobiť, prejdite na kartu Vývojár a kliknite na položku Visual Basic. Potom choďte na Vložiť a kliknutím na Modul spustite nový modul.
Poďme teraz napísať kód tak, aby stĺpec B obsahoval hodnoty stĺpca D (meno) a stĺpca E.
Syntax je uvedená ako:
Najskôr použite slučku 'for' z i = 4, pretože hodnoty v tomto prípade začínajú od 4. riadku. Smyčka bude pokračovať až do konca posledného riadku stĺpca C. Takže premennú I môžem použiť ako číslo riadku vo vnútri slučky „for“.
Potom zadajte hodnotu, ktorá sa má priradiť bunke (číslo_riadku, stĺpec B), ktorú je možné zadať ako bunky (i, „B“). Hodnota, ako bunku (číslo_riadku, stĺpec D) ).
Predpokladajme, že chcete priradiť bunku B5 = D5 & „_“ & E5, takže kód môžete jednoducho použiť ako:
Bunky (5, „B“). Hodnota = bunky (5, „D“). Hodnota & „_“ & Bunky (5, „E“). Hodnota
Teraz poďme hľadať hľadanú hodnotu v poli B5: E24. Najprv musíte zadať vyhľadávaciu hodnotu. Zoberme hodnotu (meno a oddelenie) od používateľa. Urobiť to,
- definujte ako reťazec tri premenné, meno, oddelenie a lookup_val.
- Vezmite meno od používateľa. Použite kód:
name = InputBox („Zadajte meno zamestnanca“)
Obsah zadávacieho poľa „Zadajte …“ sa zobrazí v poli s výzvou po spustení kódu. Reťazec zadaný vo výzve bude priradený k premennej názvu.
- Vezmite oddelenie od používateľa. Možno to urobiť podobne ako vyššie.
department = InputBox („Zadajte oddelenie zamestnanca“)
- Priraďte názov a oddelenie s „_“ ako oddeľovač k premennej lookup_val pomocou nasledujúcej syntaxe:
lookup_val = meno & „_“ & oddelenie
- Napíšte syntax vlookup na vyhľadanie hodnoty lookup_val v rozsahu B5: E24 ju vráti v variabilnom plate.
Inicializovať variabilný plat:
Dim plat Ako dlho
Pomocou funkcie Vlookup vyhľadajte lookup_val. Table_array môže podávať ako rozsah ( "B: F"), a plat je prítomný v 5 th kolóny. Môže sa teda použiť táto syntax:
plat = Application.WorksheetFunction.VLookup (look__val, Range („B: F“), 5, False)
- Ak chcete vytlačiť plat v okne správy, použite syntax:
MsgBox (Plat zamestnanca je „& plat)
Teraz spustite kód. V pracovnom hárku sa zobrazí okno s výzvou, do ktorého môžete zadať názov. Po zadaní názvu (Say Sashi) a kliknutí na tlačidlo OK.
Otvorí sa ďalšie políčko, v ktorom môžete vstúpiť do oddelenia. Po vstupe do oddelenia povedzte IT.
Vytlačí plat zamestnancovi.
Ak Vlookup dokáže nájsť ktoréhokoľvek zamestnanca s menom a oddelením, urobí chybu. Predpokladajme, že dáte názov „Vishnu“ a oddelenie „IT“, vráti chybu za chodu „1004“.
Ak chcete vyriešiť tento problém, môžete v kóde uviesť, že pri tomto type chyby namiesto toho vytlačte „Hodnota sa nenašla“. Urobiť to,
- Pred použitím syntaxe vlookup použite nasledujúci kód
Správa o chybe GoTo
Kontrola:
Trailing code (of Check :) will be monitored, and if it receive an error, it will go to the “message” statement
- Na konci kódu (Before End Sub) uveďte, že ak je číslo chyby 1004, vytlačte do poľa správy „Údaje o zamestnancovi nie sú k dispozícii“. To je možné vykonať pomocou syntaxe:
Správa:
Ak Err.číslo = 1004 potom
MsgBox („Údaje o zamestnancovi nie sú k dispozícii“)
Koniec Ak
Modul 1:
Sub vlookup3 ()Pre i = 4 do buniek (Rows.Count, „C“). End (xlUp). Radové
bunky (i, „B“). Hodnota = bunky (i, „D“). Hodnota & „_ “& Bunky (i,„ E “). Hodnota
Ďalej Názov iDim Ako String
Dim oddelenie Ako String
Dim lookup_val Ako String
Dim plat As Longname = InputBox („ Zadajte meno zamestnanca “)
department = InputBox („ Zadajte oddelenie zamestnanec “)
lookup_val = meno &„ _ “& departmentOn Chyba
Kontrola správy GoTo :
plat = Application.WorksheetFunction.VLookup (look__val, Range („ B: F “), 5, False)
MsgBox („ Plat zamestnanca je „& Plat) Správa:
Ak Err.číslo = 1004, potom
MsgBox („ Údaje o zamestnancovi nie sú k dispozícii “)
Koniec IfEnd Sub
Dôležité informácie o funkcii VLookup v programe Excel VBA
- Funkciu Vlookup je možné volať v programe Excel VBA pomocou nástroja WorksheetFunction.
- Syntax funkcie vlookup zostáva v aplikácii Excel VBA rovnaká.
- Keď kód VBA vlookup nemôže nájsť lookup_value, spôsobí chybu 1004.
- Chyba vo funkcii vlookup sa dá spravovať pomocou príkazu goto, ak vráti chybu.
