VLookup w VBA Excel

Vlookup to funkcja arkusza roboczego w programie Excel, ale może być również używana w VBA, funkcjonalność Vlookup jest podobna do funkcji w VBA i arkuszu, ponieważ jest to funkcja arkusza roboczego, której metoda użycia Vlookup w VBA odbywa się za pośrednictwem aplikacji. metoda i argumenty pozostają takie same.

Funkcja WYSZUKAJ.PIONOWO w Excel VBA

Funkcja WYSZUKAJ.PIONOWO w programie Excel służy do wyszukiwania wartości w tablicy i zwracania odpowiadającej jej wartości z innej kolumny. Wartość do wyszukania powinna znajdować się w pierwszej kolumnie. Należy również wspomnieć, czy należy szukać dopasowania ścisłego, czy przybliżonego. Funkcja arkusza WYSZUKAJ.PIONOWO może być używana w kodowaniu VBA. Funkcja nie jest wbudowana w VBA i dlatego można ją wywoływać tylko przy użyciu arkusza.

Funkcja WYSZUKAJ.PIONOWO w programie Excel ma następującą składnię:

W którym szukana_wartość jest wartością do wyszukania, tablica_tabeli to tabela, nr_kolumny to numer kolumny zwracanej wartości, przeszukiwany_zakres oznacza, czy dopasowanie jest dokładne czy przybliżone. wyszukiwanie_zakresu może mieć wartość TRUE / FALSE lub 0/1.

W kodzie VBA funkcja WYSZUKAJ.PIONOWO może być używana jako:

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

Jak korzystać z funkcji VLookup w programie Excel VBA?

Poniżej znajduje się kilka przykładów kodu VLookup w Excel VBA.

Możesz pobrać to VLookup w szablonie Excel VBA tutaj - VLookup w szablonie Excel VBA

Kod VLookup w Excel VBA Przykład nr 1

Zobaczmy, jak możemy wywołać funkcję WYSZUKAJ.PIONOWO w arkuszu kalkulacyjnym w Excel VBA.

Załóżmy, że masz dane o legitymacji studenckiej, nazwisku i średnich uzyskanych przez nich ocenach.

Teraz chcesz sprawdzić oceny uzyskane przez ucznia z ID 11004.

Aby wyszukać wartość, wykonaj następujące kroki:

  • Przejdź do zakładki Deweloper i kliknij Visual Basic.

  • W oknie VBA przejdź do Wstaw i kliknij Moduł.

  • Teraz napisz kod VBA VLOOKUP. Można użyć następującego kodu VBA WYSZUKAJ.PIONOWO.

Sub vlookup1 ()

Dim student_id As Long

Ciemne znaki As Long

student_id = 11004

Ustaw myrange = Range („B4: D8”)

marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Napis końcowy

Po pierwsze, zdefiniuj identyfikator studenta, który jest wartością do wyszukania. Dlatego definiujemy,

student_id = 11004

Następnie definiujemy zakres, w którym istnieje wartość i wartość zwracana. Ponieważ nasze dane są obecne w komórkach B4: D8, definiujemy zakres - myrange jako:

Ustaw myrange = Range („B4: D8”)

Na koniec wprowadzamy funkcję WYSZUKAJ.PIONOWO za pomocą funkcji arkusza roboczego w zmiennej, oznaczamy jako:

marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)

Aby wydrukować znaki w oknie komunikatu, użyjmy następującego polecenia:

MsgBox „Student z legitymacją:” & student_id & ”uzyskane” & oceny i ”oceny”

Zwróci:

Student z legitymacją: 11004 uzyskał 85 punktów.

Teraz kliknij przycisk Uruchom.

Zauważysz, że w arkuszu Excel pojawi się okno komunikatu.

Kod VLookup w Excel VBA Przykład nr 2

Załóżmy, że masz dane nazwisk pracowników i ich wynagrodzenia. Te dane są podane w kolumnach B i C. Teraz musisz napisać kod VBA WYSZUKAJ.PIONOWO w taki sposób, że podając imię i nazwisko pracownika w komórce F4, wynagrodzenie pracownika zostanie zwrócone w komórce G4.

Napiszmy kod VBA VLOOKUP.

  1. Zdefiniuj zakres, w jakim obecne są wartości, tj. Kolumny B i C.

Ustaw myrange = Range („B: C”)

  1. Zdefiniuj nazwisko pracownika i wprowadź nazwisko z komórki F4.

Ustaw nazwę = Zakres („F4”)

  1. Zdefiniuj wynagrodzenie w komórce G4.

Ustaw wynagrodzenie = zakres („G4”)

  1. Teraz wywołaj funkcję WYSZUKAJ.PIONOWO za pomocą WorksheetFunction w VBA i wprowadź ją w pensji.Value. To zwróci wartość (dane wyjściowe funkcji Vlookup) w komórce G4. Można użyć następującej składni:

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

  1. Teraz uruchom moduł. Komórka G4 będzie zawierać wynagrodzenie pracownika po uruchomieniu kodu VBA WYSZUKAJ.PIONOWO.

Załóżmy, że zmienisz w arkuszu wartość komórki F4 na „Dawid” i ponownie uruchomisz kod, a zwróci on wynagrodzenie Dawida.

Kod VLookup w Excel VBA Przykład nr 3

Załóżmy, że masz dane pracownika swojej firmy, posiadające jego identyfikator, imiona i nazwiska, dział i wynagrodzenie. Korzystając z Vlookup w VBA, chcesz uzyskać szczegółowe informacje o wynagrodzeniu pracownika, używając jego nazwiska i działu.

Ponieważ funkcja vlookup w programie Excel wyszukuje wartość lookup_value tylko w jednej kolumnie, która jest pierwszą kolumną tablicy table_array, wymagane jest, aby najpierw utworzyć kolumnę zawierającą „Nazwisko” i „Dział” każdego pracownika.

W VBA wstawmy wartości „Nazwa” i „Dział” w kolumnie B arkusza.

Aby to zrobić, przejdź do zakładki Deweloper i kliknij Visual Basic. Następnie przejdź do wstawiania i kliknij Moduł, aby rozpocząć nowy moduł.

Napiszmy teraz kod taki, że kolumna B zawiera wartości z kolumny D (nazwa) i kolumny E.

Składnia jest podana jako:

Po pierwsze, użyj pętli „for” od i = 4, ponieważ w tym przypadku wartości zaczynają się od czwartego wiersza. Pętla będzie trwała do końca ostatniego wiersza kolumny C. Tak więc zmienna i może być używana jako numer wiersza wewnątrz pętli „for”.

Następnie wprowadź wartość do przypisania do komórki (numer_wiersza, kolumna B), którą można podać jako komórki (i, „B”). Wartość jako komórka (numer_wiersza, kolumna D) i „_” i komórka (numer_wiersza, kolumna E ).

Załóżmy, że chcesz przypisać komórkę B5 = D5 i „_” i E5, możesz po prostu użyć kodu jako:

Cells (5, „B”). Value = Cells (5, „D”). Value & „_” & Cells (5, „E”). Value

Teraz poszukajmy wartości wyszukiwania w tablicy B5: E24, musisz najpierw wprowadzić wartość wyszukiwania. Weźmy wartość (nazwa i dział) od użytkownika. Aby to zrobić,

  1. zdefiniuj trzy zmienne, nazwę, dział i wartość_szukania jako ciąg.
  2. Weź nazwę od użytkownika. Użyj kodu:

name = InputBox („Wpisz imię i nazwisko pracownika”)

Treść w polu wejściowym „Enter the ..” zostanie wyświetlona w polu zachęty po uruchomieniu kodu. Ciąg wprowadzony w monicie zostanie przypisany do zmiennej nazwy.

  1. Odbierz dział od użytkownika. Można to zrobić podobnie jak powyżej.

dział = InputBox („Wprowadź dział pracownika”)

  1. Przypisz nazwę i dział z „_” jako separatorem do zmiennej lookup_val, używając następującej składni:

lookup_val = nazwa i „_” i dział

  1. Napisz składnię vlookup, aby wyszukać wartość lookup_val w zakresie B5: E24, zwróć ją w zmiennej pensji.

Zainicjuj wynagrodzenie zmienne:

Niewielka pensja As Long

Użyj funkcji Vlookup, aby znaleźć lookup_val. Tabelę table_array można podać jako zakres („B: F”), a wynagrodzenie znajduje się w piątej kolumnie. Można zatem użyć następującej składni:

salary = Application.WorksheetFunction.VLookup (lookup_val, Range („B: F”), 5, False)

  1. Aby wydrukować wynagrodzenie w oknie komunikatu, użyj składni:

MsgBox (pensja pracownika wynosi ”i wynagrodzenie)

Teraz uruchom kod. W arkuszu pojawi się okienko zachęty, w którym można wprowadzić nazwę. Po wpisaniu nazwy (Say Sashi) i kliknięciu OK.

Otworzy się kolejne okno, w którym możesz wejść do działu. Po wejściu do działu powiedz IT.

Wypisze wynagrodzenie pracownika.

Jeśli Vlookup może znaleźć dowolnego pracownika o nazwisku i dziale, wyświetli błąd. Załóżmy, że podasz nazwę „Wisznu” i dział „IT”, co zwróci błąd wykonania „1004”.

Aby rozwiązać ten problem, możesz określić w kodzie, że w przypadku tego typu błędu wypisz zamiast tego tekst „Nie znaleziono wartości”. Aby to zrobić,

  1. Przed użyciem składni vlookup użyj następującego kodu:

Komunikat o błędzie GoTo

Czek:

Końcowy kod (czeku :) będzie monitorowany i jeśli otrzyma jakikolwiek błąd, przejdzie do instrukcji „komunikat”

  1. Na końcu kodu (Before End Sub) określ, że jeśli numer błędu to 1004, wydrukuj w oknie komunikatu „Brak danych pracownika”. Można to zrobić za pomocą składni:

Wiadomość:

Jeśli Err.Number = 1004 Wtedy

MsgBox („Brak danych pracownika”)

Koniec, jeśli

Moduł 1:

Sub vlookup3 ()

Dla i = 4 To Cells (Rows.Count, „C”). End (xlUp) .Row

Cells (i, „B”). Value = Cells (i, „D”). Value & „_” & Cells (i, „E”). Value

Następna nazwa iDim As String

Dim dział As String

Dim lookup_val As String

Minimalne wynagrodzenie As Longname = InputBox („Wpisz imię i nazwisko pracownika”)

dział = InputBox („Wprowadź dział pracownika”)

lookup_val = name & „_” & DepartmentOn Error GoTo Message

czek:

salary = Application.WorksheetFunction.VLookup (lookup_val, Range („B: F”), 5, False)

MsgBox („Wynagrodzenie pracownika wynosi” i wynagrodzenie) Wiadomość:

Jeśli Err.Number = 1004 Wtedy

MsgBox („Brak danych pracownika”)

End IfEnd Sub

Rzeczy do zapamiętania dotyczące funkcji VLookup w programie Excel VBA

  • Funkcję Vlookup można wywołać w Excel VBA za pomocą WorksheetFunction.
  • Składnia funkcji vlookup pozostaje taka sama w Excel VBA.
  • Gdy kod vlookup VBA nie może znaleźć lookup_value, zwróci błąd 1004.
  • Błąd w funkcji vlookup można zarządzać za pomocą instrukcji goto, jeśli zwraca błąd.