VLOOKUP v aplikaci Excel VBA - Jak napsat kód VLOOKUP do VBA?

Obsah

Funkce VLOOKUP v aplikaci Excel VBA

Vlookup je funkce listu v aplikaci Excel, ale lze ji také použít ve VBA, funkce Vlookup je podobná funkcím ve VBA i v listu, protože se jedná o funkci listu metoda pro použití Vlookup ve VBA je prostřednictvím Application.WorksheetFunction metoda a argumenty zůstávají stejné.

Funkce VLOOKUP v aplikaci Excel VBA

Funkce VLOOKUP v aplikaci Excel se používá k hledání hodnoty v poli a vrácení odpovídající hodnoty z jiného sloupce. Hodnota pro hledání by měla být uvedena v prvním sloupci. Je také nutné zmínit, zda hledat přesnou shodu nebo přibližnou shodu. Funkci listu VLOOKUP lze použít při kódování VBA. Tato funkce není vestavěná VBA, a proto může volat pouze pomocí listu.

Funkce VLOOKUP v aplikaci Excel má následující syntaxi:

Ve kterém je hledaná hodnota lookup_value, table_arrray je tabulka, col_index_num je číslo sloupce návratové hodnoty, range_lookup znamená, zda je shoda přesná nebo přibližná. range_lookup může být TRUE / FALSE nebo 0/1.

V kódu VBA lze funkci VLOOKUP použít jako:

Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)

Jak používat VLookup v aplikaci Excel VBA?

Níže uvádíme několik příkladů kódu VLookup v aplikaci Excel VBA.

Kód VLookup v aplikaci Excel VBA Příklad č. 1

Podívejme se, jak můžeme volat funkci listu VLOOKUP v aplikaci Excel VBA.

Předpokládejme, že obsahuje údaje o ID, jménu a průměrných známkách, které studenti získali.

Nyní chcete vyhledat známky získané studentem s ID 11004.

Chcete-li vyhledat hodnotu, postupujte podle následujících kroků:

Přejděte na kartu Vývojář a klikněte na Visual Basic.

V okně VBA přejděte na Vložit a klikněte na Modul.

Nyní napište kód VBA VLOOKUP. Lze použít následující 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

Nejprve definujte ID studenta, což je hodnota k vyhledání. Proto definujeme,

student_id = 11004

Dále definujeme rozsah, ve kterém hodnota a návratová hodnota existují. Protože naše data jsou přítomna v buňkách B4: D8, definujeme rozsah-rozsah jako:

Nastavit myrange = Range („B4: D8“)

Nakonec zadáme funkci VLOOKUP pomocí funkce List v proměnné, označí se jako:

známky = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Chcete-li vytisknout značky v okně se zprávou, použijte následující příkaz:

MsgBox „Student s ID:“ & student_id & „získal“ & známky & „známky“

Vrátí se:

Student s ID: 11004 získal 85 známek.

Nyní klikněte na tlačítko spuštění.

Zjistíte, že se v listu aplikace Excel zobrazí okno se zprávou.

Kód VLookup v příkladu 2 aplikace Excel VBA

Předpokládejme, že máte údaje o jménech zaměstnanců a jejich platu. Tato data jsou uvedena ve sloupcích B a C. Nyní je třeba napsat VLOOKUP kód VBA tak, že vzhledem k jménu zaměstnance v buňce F4 bude plat zaměstnance vrácen v buňce G4.

Napíšeme kód VBA VLOOKUP.

Definujte rozsah, ve kterém jsou hodnoty přítomny, tj. Sloupce B a C.

Nastavit myrange = Range („B: C“)

Definujte jméno zaměstnance a zadejte jméno z buňky F4.

Název sady = Rozsah („F4“)

Definujte plat jako buňku G4.

Nastavit plat = Rozsah („G4“)

Nyní zavolejte funkci VLOOKUP pomocí WorksheetFunction ve VBA a zadejte ji v plat.Value. Tím se vrátí hodnota (výstup funkce Vlookup) v buňce G4. Lze použít následující syntaxi:

plate.Value = Application.WorksheetFunction.VLookup (name, myrange, 2, False)

Nyní spusťte modul. Buňka G4 bude obsahovat plat zaměstnance po spuštění kódu VBA VLOOKUP.

Předpokládejme, že v listu změníte hodnotu buňky F4 na „David“ a znovu spustíte kód, který vrátí Davidův plat.

Kód VLookup v aplikaci Excel VBA Příklad č. 3

Předpokládejme, že máte k dispozici údaje o zaměstnancích vaší společnosti s jejich ID, jmény, oddělením a platem. Pomocí Vlookup ve VBA chcete získat podrobnosti o platu zaměstnance pomocí jeho jména a oddělení.

Jelikož funkce vlookup v aplikaci Excel prohledává hodnotu lookup_value pouze v jednom sloupci, což je první sloupec table_array, je nutné nejprve vytvořit sloupec obsahující „Jméno“ a „Oddělení“ každého zaměstnance.

Ve VBA vložme hodnoty „Název“ a „Oddělení“ do sloupce B listu.

Chcete-li to provést, přejděte na kartu Vývojář a klikněte na Visual Basic. Poté přejděte na Vložit a kliknutím na Modul spusťte nový modul.

Nyní napíšeme kód, takže sloupec B obsahuje hodnoty sloupce D (název) a sloupce E.

Syntaxe je uvedena jako:

Za prvé, použít ‚pro‘ smyčky z i = 4, protože jsou hodnoty počínaje 4 ^th řádek v tomto případě. Smyčka bude pokračovat až do konce posledního řádku sloupce C. Proměnnou I tedy lze použít jako číslo řádku uvnitř smyčky 'pro'.

Poté zadejte hodnotu, která se má přiřadit buňce (číslo_řádku, sloupec B), kterou lze zadat jako buňky (i, „B“). Hodnota, jako buňku (číslo_řádku, sloupec D) ).

Předpokládejme, že chcete přiřadit buňku B5 = D5 & “_” & E5, můžete jednoduše použít kód jako:

Buňky (5, „B“). Hodnota = Buňky (5, „D“). Hodnota & „_“ & Buňky (5, „E“). Hodnota

Nyní se podívejme na vyhledávací hodnotu v poli B5: E24. Nejprve musíte zadat vyhledávací hodnotu. Vezměme hodnotu (jméno a oddělení) od uživatele. Udělat toto,

definujte jako řetězec tři proměnné, název, oddělení a lookup_val.
Vezměte jméno od uživatele. Použijte kód:

name = InputBox („Zadejte jméno zaměstnance“)

Po spuštění kódu se v zadávacím poli zobrazí obsah vstupního pole „Zadejte …“. Řetězec zadaný ve výzvě bude přiřazen k proměnné názvu.

Vezměte oddělení od uživatele. To lze provést podobně jako výše.

department = InputBox („Zadejte oddělení zaměstnance“)

Přiřaďte název a oddělení s „_“ jako oddělovač proměnné lookup_val pomocí následující syntaxe:

lookup_val = jméno & „_“ & oddělení

Napište syntaxi vlookup a vyhledejte lookup_val v rozsahu B5: E24 jej vrátí v proměnlivém platu.

Inicializujte variabilní plat:

Dim plat Jak dlouho

Pomocí funkce Vlookup vyhledejte lookup_val. Table_array může být podáván jako rozsah ( „B: F“), a plat je přítomen v 5 ^th kolony. Lze tedy použít následující syntaxi:

plat = Application.WorksheetFunction.VLookup (look__val, Range („B: F“), 5, False)

Chcete-li tisknout mzdu ve zprávě, použijte syntaxi:

MsgBox (Plat zaměstnance je „& plat)

Nyní spusťte kód. V listu, ve kterém můžete zadat název, se zobrazí okno s výzvou. Po zadání jména (Say Sashi) a kliknutí na OK.

Otevře se další pole, ve kterém můžete vstoupit do oddělení. Po vstupu do oddělení řekněte IT.

Vytiskne plat zaměstnance.

Pokud Vlookup najde libovolného zaměstnance se jménem a oddělením, zobrazí chybu. Předpokládejme, že zadáte název „Vishnu“ a oddělení „IT“, vrátí chybu běhu „1004“.

Chcete-li tento problém vyřešit, můžete v kódu určit, že u tohoto typu chyby bude místo toho vytištěno „Hodnota nenalezena“. Udělat toto,

Před použitím syntaxe vlookup použijte následující kód-

Zpráva o chybě GoTo

Šek:

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) zadejte, že pokud je číslo chyby 1004, vytiskněte v okně se zprávou „Data zaměstnanců nejsou k dispozici“. To lze provést pomocí syntaxe:

Zpráva:

Pokud Err.číslo = 1004 potom

MsgBox („Údaje o zaměstnancích nejsou k dispozici“)

Konec, pokud

Modul 1:

Sub vlookup3 ()
Pro i = 4 do buněk (Rows.Count, „C“). End (xlUp). Row
Cells (i, „B“). Value = Cells (i, „D“). Value & „_ „& Buňky (i,„ E “). Hodnota
Další Název iDim Jako String
Dim oddělení Jako String
Dim vyhledávání_val Jako String
Dim plat Jak Longname = InputBox („ Zadejte jméno zaměstnance “)
oddělení = InputBox („ Zadejte oddělení zaměstnanec “)
lookup_val = jméno &„ _ “& departmentOn Chyba
Kontrola zprávy GoTo :
plat = Application.WorksheetFunction.VLookup (look__val, Range („ B: F “), 5, False)
MsgBox („ Plat zaměstnance je „& Plat) Zpráva:
Je-li Err.číslo = 1004, pak
MsgBox („ Údaje o zaměstnancích nejsou k dispozici “)
Konec IfEnd Sub