Vlookup to funkcja arkusza roboczego w programie Excel, ale może być również używana w VBA, funkcjonalność Vlookup jest podobna do funkcjonalności 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że 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, kolumna_index_num 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.
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 studenta, nazwisku i średniej uzyskanej przez nich ocenie.
Teraz chcesz sprawdzić oceny uzyskane przez ucznia o dowodzie tożsamości 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
Dim marks As Long
student_id = 11004
Set myrange = Range („B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Po pierwsze, zdefiniuj identyfikator ucznia, czyli wartość 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, oznaczając 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” i oceny i „oceny”
Zwróci:
Uczeń o legitymacji 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 dotyczące 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 biorąc pod uwagę nazwisko pracownika w komórce F4, wynagrodzenie pracownika zostanie zwrócone w komórce G4.
Napiszmy kod VBA VLOOKUP.
- Określ zakres, w jakim występują wartości, tj. Kolumny B i C.
Ustaw myrange = Range („B: C”)
- Zdefiniuj imię i nazwisko pracownika i wprowadź je 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 „David” 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, 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 przeszukuje wartość lookup_value tylko w jednej kolumnie, która jest pierwszą kolumną tabeli 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 Wstaw 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 kontynuowana 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 „_” & 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. Najpierw musisz wprowadzić wartość wyszukiwania. Weźmy wartość (nazwa i dział) od użytkownika. Aby to zrobić,
- zdefiniuj trzy zmienne, nazwę, dział i wartość wyszukiwania jako ciąg.
- Weź nazwę od użytkownika. Użyj kodu:
name = InputBox („Wpisz imię i nazwisko pracownika”)
Treść w polu wprowadzania „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 zwraca 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ę okno zachęty, w którym można wprowadzić nazwę. Po wprowadzeniu 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 błąd, przejdzie do instrukcji „wiadomość”
- Na końcu kodu (Before End Sub) określ, że jeśli numer błędu to 1004, wpisz 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”)
Zakończ, jeśli
Moduł 1:
Sub vlookup3 ()For i = 4 To Cells (Rows.Count, „C”). End (xlUp) .Row
Cells (i, „B”). Value = Cells (i, „D”). Value & „_ ”& Cells (i,„ E ”). Wartość
Następny iDim name As String
Dim dział As String
Dim lookup_val As String
Dim pensja As Longname = InputBox („ Wpisz imię i nazwisko pracownika ”)
dział = InputBox („ Wpisz dział pracownik ”)
lookup_val = imię i nazwisko &„ _ ”& działOn Błąd GoTo
Sprawdzenie wiadomości :
salary = Application.WorksheetFunction.VLookup (lookup_val, Range („ B: F ”), 5, False)
MsgBox („ Wynagrodzenie pracownika wynosi ”I wynagrodzenie) Komunikat:
Jeśli Err.Number = 1004 Then
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, wygeneruje błąd 1004.
- Błąd w funkcji vlookup można zarządzać za pomocą instrukcji goto, jeśli zwraca błąd.
