Vlookup este o funcție de foaie de lucru în Excel, dar poate fi utilizată și în VBA, funcționalitatea Vlookup este similară cu funcționalitatea din VBA și în ambele foaie de lucru, deoarece este o funcție de foaie de lucru, metoda de a utiliza Vlookup în VBA este prin aplicație. metoda și argumentele rămân aceleași.
Funcția VLOOKUP în Excel VBA
Funcția VLOOKUP în Excel este utilizată pentru a căuta o valoare într-o matrice și pentru a returna valoarea corespunzătoare din altă coloană. Valoarea de căutat ar trebui să fie prezentă în prima coloană. De asemenea, este necesar să menționați dacă trebuie să căutați o potrivire exactă sau o potrivire aproximativă. Funcția Foaie de lucru VLOOKUP poate fi utilizată în codarea VBA. Funcția nu este VBA încorporată și, prin urmare, poate apela numai utilizând foaia de lucru.
Funcția VLOOKUP în Excel are următoarea sintaxă:
În care, valoare_consultare este valoarea de căutat, tabel_arrray este tabelul, col_index_num este numărul coloanei valorii returnate, range_lookup înseamnă dacă potrivirea este exactă sau aproximativă. range_lookup poate fi ADEVĂRAT / FALS sau 0/1.
În codul VBA, funcția VLOOKUP poate fi utilizată ca:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Cum se folosește VLookup în Excel VBA?
Mai jos sunt câteva exemple de cod VLookup în Excel VBA.
Cod VLookup în Excel VBA Exemplul # 1
Să vedem cum putem apela funcția foaie de lucru VLOOKUP în Excel VBA.
Să presupunem că vă date despre ID-ul elevilor, numele și nota medie obținută de aceștia.
Acum, doriți să căutați notele obținute de un student cu ID 11004.
Pentru a căuta valoarea, urmați pașii următori:
- Accesați fila Dezvoltator și faceți clic pe Visual Basic.
- Sub fereastra VBA, accesați Insert și faceți clic pe Module.
- Acum, scrieți codul VBA VLOOKUP. Se poate utiliza următorul cod VBA VLOOKUP.
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
În primul rând, definiți un ID de student, care este valoarea de căutat. Prin urmare, definim,
student_id = 11004
Apoi, definim intervalul în care există valoarea și valoarea returnată. Întrucât datele noastre sunt prezente în celulele B4: D8, definim o gamă-gamă ca:
Set myrange = Range („B4: D8”)
În cele din urmă, introducem funcția VLOOKUP folosind funcția Foaie de lucru într-o variabilă, marchează ca:
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Pentru a imprima semnele într-o casetă de mesaj, permiteți-ne să folosim următoarea comandă:
MsgBox „Student cu ID:” & student_id & ”obținut” & note & ”note”
Se va întoarce:
Un student cu ID: 11004 a obținut 85 de note.
Acum, faceți clic pe butonul Executare.
Veți observa că va apărea o casetă de mesaj în foaia Excel.
Cod VLookup în Excel VBA Exemplul # 2
Să presupunem că aveți datele despre numele angajaților și salariul acestora. Aceste date primesc coloanele B și C. Acum, trebuie să scrieți un cod VBA VLOOKUP astfel încât, având în vedere numele angajatului într-o celulă, F4, salariul angajatului va fi returnat în celula G4.
Să scriem codul VBA VLOOKUP.
- Definiți intervalul în care sunt prezente valorile, adică coloanele B și C.
Set myrange = Range („B: C”)
- Definiți numele angajatului și introduceți numele din celula F4.
Numele setului = Interval („F4”)
- Definiți salariul ca celula G4.
Salariu stabilit = Interval („G4”)
- Acum, apelați funcția VLOOKUP folosind WorksheetFunction în VBA și introduceți-o în salariu. Aceasta va returna valoarea (ieșirea funcției Vlookup) în celula G4. Se poate utiliza următoarea sintaxă:
salariu.Valoare = Aplicație.FuncțieFuncție.VCercare (nume, gama mea, 2, Fals)
- Acum, rulați modulul. Celula G4 va conține salariul angajatului după ce executați codul VBA VLOOKUP.
Să presupunem că schimbați valoarea celulei F4 în „David” în foaia de lucru și re-rulați codul, iar acesta va returna salariul lui David.
Cod VLookup în Excel VBA Exemplul nr. 3
Să presupunem că aveți datele despre angajatul companiei dvs., având ID-ul, numele, departamentul și salariul acestora. Folosind Vlookup în VBA, doriți să obțineți detaliile salariale ale unui angajat folosind numele și departamentul său.
Deoarece funcția vlookup în Excel caută valoarea de căutare doar într-o singură coloană, care este prima coloană a tabelului, este necesar să faceți mai întâi o coloană care să conțină „Nume” și „Departament” pentru fiecare angajat.
În VBA, permiteți-ne să introducem valorile „Nume” și „Departament” în coloana B a foii de lucru.
Pentru a face acest lucru, accesați fila Dezvoltator și faceți clic pe Visual Basic. Apoi accesați Insert și faceți clic pe Module pentru a porni un nou modul.
Să scriem acum cod, astfel încât coloana B să conțină valorile coloanei D (nume) și coloanei E.
Sintaxa este dată ca:
În primul rând, utilizați o buclă „pentru” de la i = 4, deoarece valorile încep de la al 4- lea rând în acest caz. Bucla va continua până la sfârșitul ultimului rând al coloanei C. Deci, variabila I poate fi utilizată ca număr de rând în bucla „pentru”.
Apoi introduceți valoarea de atribuit pentru Celulă (număr_ rând, coloană B), care poate fi dată ca celule (i, „B”). ).
Să presupunem că doriți să atribuiți celula B5 = D5 & „_” & E5, puteți pur și simplu utiliza codul ca:
Celule (5, „B”). Valoare = Celule (5, „D”). Valoare & „_” & Celule (5, „E”). Valoare
Acum, să căutăm valoarea de căutare în matricea B5: E24. Mai întâi trebuie să introduceți valoarea de căutare. Să luăm valoarea (Nume și Departament) de la utilizator. Pentru a face acest lucru,
- definiți trei variabile, nume, departament și lookup_val ca un șir.
- Luați numele introdus de la utilizator. Folosiți codul:
name = InputBox („Introduceți numele angajatului”)
Conținutul din caseta de introducere „Introduceți …” va fi afișat în caseta de solicitare atunci când rulați codul. Șirul introdus în prompt va fi atribuit variabilei de nume.
- Ia departamentul de la utilizator. Se poate face în mod similar ca mai sus.
departament = InputBox („Introduceți departamentul angajatului”)
- Atribuiți numele și departamentul cu „_” ca separator pentru variabila lookup_val utilizând următoarea sintaxă:
lookup_val = nume & „_” & departament
- Scrieți sintaxa vlookup pentru a căuta lookup_val în intervalul B5: E24 îl returnează într-un salariu variabil.
Inițializați salariul variabil:
Salariu slab Atât de mult
Utilizați funcția Vlookup pentru a găsi lookup_val. Table_array poate fi dat ca Range („B: F”), iar salariul este prezent în coloana a 5- a . Următoarea sintaxă poate fi astfel utilizată:
salariu = Application.WorksheetFunction.VLookup (lookup_val, Range („B: F”), 5, False)
- Pentru a imprima salariul într-o casetă de mesaje, utilizați sintaxa:
MsgBox (Salariul angajatului este ”și salariu)
Acum, rulați codul. În foaia de lucru va apărea o casetă de prompt în care puteți introduce numele. După ce introduceți numele (Say Sashi) și faceți clic pe OK.
Se va deschide o altă casetă în care puteți intra în departament. După ce intri în departament, spune IT.
Se va imprima salariul angajatului.
Dacă Vlookup poate găsi un angajat cu numele și departamentul, va da o eroare. Să presupunem că dați numele „Vishnu” și departamentul „IT”, va returna eroarea de execuție '1004'.
Pentru a rezolva această problemă, puteți specifica în cod că, pe acest tip de eroare, tipăriți „Valoarea nu a fost găsită”. Pentru a face acest lucru,
- Înainte de a utiliza sintaxa vlookup, utilizați următorul cod-
La mesajul de eroare GoTo
Verifica:
Codul final (de verificare :) va fi monitorizat și, dacă primește o eroare, va merge la declarația „mesaj”
- La sfârșitul codului (Before End Sub), specificați că, dacă numărul de eroare este 1004, apoi imprimați în caseta de mesaj „Datele angajaților nu sunt prezente”. Acest lucru se poate face folosind sintaxa:
Mesaj:
Dacă Err.Number = 1004 Atunci
MsgBox („Datele angajaților nu sunt prezente”)
End If
Modulul 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 ”). Valoare
Următorul nume iDim Ca șir
Dim departament Ca șir
Dim căutare_valor Ca șir
Dim salariu Ca nume lung = InputBox („ Introduceți numele angajatului ”)
departament = InputBox („ Introduceți departamentul de angajatul“)
lookup_val = numele &«»Eroare & departmentOn GoTo mesaj _
cec:
salariu = Application.WorksheetFunction.VLookup (lookup_val, Range («B: F»), 5, false)
MsgBox („salariul angajatului este ”Și salariu) Mesaj:
If Err.Number = 1004 Apoi
MsgBox („ Datele angajaților nu sunt prezente ”)
End IfEnd Sub
Lucruri de reținut despre VLookup în Excel VBA
- Funcția Vlookup poate fi apelată în Excel VBA utilizând WorksheetFunction.
- Sintaxa funcției vlookup rămâne aceeași în Excel VBA.
- Când codul de căutare VBA nu poate găsi lookup_value, va da o eroare 1004.
- Eroarea din funcția vlookup poate fi gestionată folosind o instrucțiune goto dacă returnează o eroare.
