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.
- Zdefiniuj zakres, w jakim obecne są wartości, tj. Kolumny B i C.
Ustaw myrange = Range („B: C”)
- Zdefiniuj nazwisko pracownika i wprowadź nazwisko z komórki F4.
Ustaw nazwę = Zakres („F4”)
- Zdefiniuj wynagrodzenie w komórce G4.
Ustaw wynagrodzenie = zakres („G4”)
- 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)
- 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ć,
- zdefiniuj trzy zmienne, nazwę, dział i wartość_szukania jako ciąg.
- 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.
- Odbierz dział od użytkownika. Można to zrobić podobnie jak powyżej.
dział = InputBox („Wprowadź dział pracownika”)
- 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ł
- 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)
- 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ć,
- 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”
- 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.