Vlookup è una funzione del foglio di lavoro in Excel ma può essere utilizzata anche in VBA, la funzionalità di Vlookup è simile alla funzionalità in VBA e in entrambi i fogli di lavoro, poiché è una funzione del foglio di lavoro il metodo per utilizzare Vlookup in VBA è tramite Application. metodo e gli argomenti rimangono gli stessi.
Funzione CERCA.VERT in Excel VBA
La funzione CERCA.VERT in Excel viene utilizzata per cercare un valore in una matrice e restituire il valore corrispondente da un'altra colonna. Il valore da cercare dovrebbe essere presente nella prima colonna. È inoltre necessario indicare se cercare una corrispondenza esatta o una corrispondenza approssimativa. La funzione del foglio di lavoro CERCA.VERT può essere utilizzata nella codifica VBA. La funzione non è incorporata in VBA e quindi può essere chiamata solo utilizzando il foglio di lavoro.
La funzione CERCA.VERT in Excel ha la seguente sintassi:
In cui, lookup_value è il valore da cercare, table_arrray è la tabella, col_index_num è il numero di colonna del valore restituito, range_lookup indica se la corrispondenza è esatta o approssimativa. range_lookup può essere VERO / FALSO o 0/1.
Nel codice VBA, la funzione CERCA.VERT può essere utilizzata come:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Come utilizzare VLookup in Excel VBA?
Di seguito sono riportati alcuni esempi di codice VLookup in Excel VBA.
Puoi scaricare questo VLookup nel modello VBA di Excel qui - VLookup nel modello VBA di Excel
Codice VLookup in Excel VBA Esempio n. 1
Vediamo come possiamo chiamare la funzione del foglio di lavoro CERCA.VERT in Excel VBA.
Supponiamo di avere i dati dell'ID studente, del nome e dei voti medi da loro ottenuti.
Adesso, vuoi cercare i voti ottenuti da uno studente, con ID 11004.
Per cercare il valore, attenersi alla seguente procedura:
- Vai alla scheda Sviluppatore e fai clic su Visual Basic.
- Sotto la finestra VBA, vai su Inserisci e fai clic su Modulo.
- Ora scrivi il codice VBA VLOOKUP. È possibile utilizzare il seguente codice CERCA.VERT VBA.
Sub vlookup1 ()
Dim student_id As Long
Segni fiochi As Long
student_id = 11004
Imposta myrange = Range ("B4: D8")
mark = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
In primo luogo, definire un ID studente, che è il valore da cercare. Pertanto, definiamo,
student_id = 11004
Successivamente, definiamo l'intervallo in cui esistono il valore e il valore restituito. Poiché i nostri dati sono presenti nelle celle B4: D8, definiamo un intervallo- myrange come:
Imposta myrange = Range ("B4: D8")
Infine, inseriamo la funzione CERCA.VERT utilizzando la funzione Foglio di lavoro in una variabile, contrassegnata come:
mark = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Per stampare i segni in una finestra di messaggio, utilizziamo il seguente comando:
MsgBox "Studente con ID:" e student_id & "ha ottenuto" e voti e "voti"
Tornerà:
Studente con ID: 11004 ha ottenuto 85 punti.
Ora fai clic sul pulsante Esegui.
Noterai che nel foglio Excel apparirà una finestra di messaggio.
Codice VLookup in Excel VBA Esempio n. 2
Supponiamo di avere i dati dei nomi dei dipendenti e del loro stipendio. A questi dati vengono assegnate le colonne B e C. Ora, è necessario scrivere un codice CERCA.VERT VBA tale che, dato un nome del dipendente in una cella, F4, lo stipendio del dipendente venga restituito nella cella G4.
Scriviamo il codice CERCA.VERT VBA.
- Definire l'intervallo in cui sono presenti i valori, ovvero la colonna B e C.
Imposta myrange = Range ("B: C")
- Definisci il nome del dipendente e inserisci il nome dalla cella F4.
Imposta nome = Intervallo ("F4")
- Definisci stipendio come cella G4.
Imposta stipendio = Intervallo ("G4")
- Ora, chiama la funzione CERCA.VERT utilizzando WorksheetFunction in VBA e inseriscilo in salary.Value. Questo restituirà il valore (output della funzione Vlookup) nella cella G4. È possibile utilizzare la seguente sintassi:
salary.Value = Application.WorksheetFunction.VLookup (name, myrange, 2, False)
- Ora esegui il modulo. La cella G4 conterrà lo stipendio del dipendente dopo aver eseguito il codice VBA VLOOKUP.
Supponiamo di modificare il valore della cella F4 in "David" nel foglio di lavoro e di rieseguire il codice, restituirà lo stipendio di David.
Codice VLookup in Excel VBA Esempio n. 3
Supponi di avere i dati del dipendente della tua azienda, con il loro ID, i nomi, il dipartimento e lo stipendio. Utilizzando Vlookup in VBA, desideri ottenere i dettagli dello stipendio di un dipendente utilizzando il suo nome e il suo dipartimento.
Poiché la funzione vlookup in Excel ricerca il lookup_value in una sola colonna, che è la prima colonna di table_array, è necessario prima creare una colonna contenente il "Nome" e il "Dipartimento" di ciascun dipendente.
In VBA, inseriamo i valori "Nome" e "Dipartimento" nella colonna B del foglio di lavoro.
Per fare ciò, vai alla scheda Sviluppatore e fai clic su Visual Basic. Quindi vai su Inserisci e fai clic su Modulo per avviare un nuovo modulo.
Scriviamo ora il codice, in modo tale che la colonna B contenga i valori della colonna D (nome) e della colonna E.
La sintassi è data come:
In primo luogo, usa un ciclo "for" da i = 4 poiché i valori iniziano dalla quarta riga in questo caso. Il ciclo continuerà fino alla fine dell'ultima riga della colonna C. Quindi, la variabile i può essere utilizzata come numero di riga all'interno del ciclo "for".
Quindi inserisci il valore da assegnare per Cella (numero_riga, colonna B), che può essere fornito come Celle (i, "B"). Valore, come Cella (numero_riga, colonna D) & "_" & Cella (numero_riga, colonna E ).
Supponiamo di voler assegnare la cella B5 = D5 e "_" ed E5, puoi semplicemente usare il codice come:
Celle (5, "B"). Valore = Celle (5, "D"). Valore & "_" & Celle (5, "E"). Valore
Ora, cerchiamo il valore di ricerca nell'array B5: E24, devi prima inserire il valore di ricerca. Prendiamo il valore (Nome e Reparto) dall'utente. Per fare questo,
- definire tre variabili, nome, reparto e lookup_val come una stringa.
- Prendi il nome inserito dall'utente. Usa il codice:
name = InputBox ("Immettere il nome dell'impiegato")
Il contenuto nella casella di immissione "Immettere il .." verrà visualizzato nella casella di richiesta quando si esegue il codice. La stringa inserita nel prompt verrà assegnata alla variabile nome.
- Prendi il reparto dall'utente. Può essere fatto in modo simile come sopra.
dipartimento = InputBox ("Inserisci il dipartimento del dipendente")
- Assegna il nome e il reparto con "_" come separatore alla variabile lookup_val utilizzando la seguente sintassi:
lookup_val = name & "_" & department
- Scrivi la sintassi di vlookup per cercare lookup_val nell'intervallo B5: E24 restituiscilo in uno stipendio variabile.
Inizializza lo stipendio variabile:
Dim stipendio As Long
Usa la funzione Vlookup per trovare lookup_val. Il table_array può essere dato come Range (“B: F”) e lo stipendio è presente nella quinta colonna. È quindi possibile utilizzare la seguente sintassi:
stipendio = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)
- Per stampare lo stipendio in una finestra di messaggio, utilizza la sintassi:
MsgBox (Lo stipendio del dipendente è "e stipendio)
Ora esegui il codice. Apparirà una finestra di messaggio nel foglio di lavoro in cui è possibile inserire il nome. Dopo aver inserito il nome (Say Sashi) e fare clic su OK.
Si aprirà un'altra casella in cui potrai entrare nel reparto. Dopo essere entrato nel dipartimento, dì IT.
Stamperà lo stipendio del dipendente.
Se Vlookup riesce a trovare un dipendente con il nome e il dipartimento, restituirà un errore. Supponiamo che tu dia il nome "Vishnu" e il reparto "IT", restituirà l'errore di runtime "1004".
Per risolvere questo problema, è possibile specificare nel codice, che su questo tipo di errore, stampare invece "Valore non trovato". Per fare questo,
- Prima di utilizzare la sintassi di vlookup, utilizzare il codice seguente:
On Error GoTo Message
Dai un'occhiata:
Il codice finale (di Check :) verrà monitorato e, se riceve un errore, andrà all'istruzione "message"
- Alla fine del codice (Before End Sub), specificare che se il numero di errore è 1004, stampare nella finestra di messaggio "Dati del dipendente non presenti". Questo può essere fatto usando la sintassi:
Messaggio:
Se Err.Number = 1004 Allora
MsgBox ("Dati dipendenti non presenti")
Finisci se
Modulo 1:
Sub vlookup3 ()Per i = 4 To Cells (Rows.Count, "C"). End (xlUp) .Row
Celle (i, "B"). Valore = Celle (i, "D"). Valore & "_" & Celle (i, "E"). Valore
Nome iDim successivo As String
Dim department As String
Dim lookup_val As String
Dim stipendio As Longname = InputBox ("Immettere il nome del dipendente")
dipartimento = InputBox ("Inserisci il dipartimento del dipendente")
lookup_val = name & "_" & departmentOn Error GoTo Message
dai un'occhiata:
stipendio = Application.WorksheetFunction.VLookup (lookup_val, Range ("B: F"), 5, False)
MsgBox ("Lo stipendio del dipendente è" e stipendio) Messaggio:
Se Err.Number = 1004 Allora
MsgBox ("Dati dipendenti non presenti")
End IfEnd Sub
Cose da ricordare su VLookup in Excel VBA
- La funzione Vlookup può essere chiamata in Excel VBA utilizzando WorksheetFunction.
- La sintassi della funzione vlookup rimane la stessa in Excel VBA.
- Quando il codice VBA vlookup non riesce a trovare lookup_value, restituirà un errore 1004.
- L'errore nella funzione vlookup può essere gestito utilizzando un'istruzione goto se restituisce un errore.
