Come automatizzare Vlookup utilizzando Excel VBA

Vlookup è una funzione essenziale in Excel ed è diventata una parte importante dell'elaborazione dei dati. Fornisce una serie di funzioni che è possibile combinare con un database completo.

Tuttavia, se desideri automatizzare questa funzione, puoi farlo, soprattutto se sai come utilizzare il linguaggio di automazione disponibile in Excel e VBA. Ecco come automatizzare la funzione vlookup in Excel VBA .

Formula della funzione Vlookup in Excel

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Significato degli argomenti nella formula:

• lookup_value: il valore sottostante che desideri cercare nell'array.
• lookup_array: dati di origine, lookup_value verrà utilizzato come riferimento.
• column_index: la colonna restituisce il valore.
• exact_match/partial_match: utilizzare 0 o 1 per specificare il tipo di combinazione.

Funzione VBA Vlookup in Excel

L'utilizzo della funzione Vlookup non è molto diverso quando la si utilizza direttamente in Excel o tramite VBA. Sia che un analista di dati elabori righe di informazioni o che un responsabile del magazzino lavori costantemente con nuovi prodotti, la funzione vlookup è utile.

Quando si lavora con un intervallo di ricerca dinamico, è preferibile automatizzare la formula per evitare di dover manipolare la stessa formula più volte in Excel. Automatizzando la funzione Vlookup in VBA, puoi eseguire calcoli su più colonne con un clic.

Illustrazione

Questo set di dati è composto da due parti: una tabella di ricerca e punti dati di destinazione. La tabella di ricerca contiene 3 colonne, con i dati nei due campi Sottocategoria e Nome prodotto:

La tabella di destinazione ha le colonne appropriate, nessuna sottocartella o dati sul nome del prodotto. Tieni presente che i valori nella colonna ID ordine compaiono anche nella stessa colonna nella tabella di ricerca:

1. Effettuare le impostazioni necessarie

Inizia aprendo l'editor di codice (premi Alt + F11 o vai alla scheda Sviluppatore ) in una nuova cartella di lavoro di Excel e aggiungi il modulo per avviare la codifica. Nell'editor del codice, aggiungi le seguenti righe nella parte superiore della finestra di programmazione per creare una subroutine:

Sub vlookup_fn1()End Sub

la subroutine è un contenitore per il codice VBA ed è necessaria per eseguirla correttamente. Un'altra alternativa è creare un modulo utente VBA per rendere l'interfaccia utente più interattiva.

2. Dichiarare variabili e creare ambiti di riferimento

Innanzitutto, devi dichiarare i tipi di dati delle variabili utilizzando il comando Dim:

Dim i as integer, lastrow as long

Successivamente, crea una variabile lastrow per memorizzare il valore dell'ultima riga popolata nell'intervallo di ricerca. La funzione lastrow utilizza la funzione end(xldown) per calcolare l'ultimo riferimento di riga in un intervallo specifico.

In questo esempio, la variabile lastrow contiene il valore dell'ultima riga riempita nell'intervallo di ricerca, in particolare 17.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

Nell'esempio precedente, la tabella di destinazione si estende da A2:C17 e la tabella di origine si estende da F2:H17. La formula vlookup scorrerà ciascun valore nella colonna A, lo cercherà nella tabella di origine e incollerà le voci combinate nelle colonne B e C.

Tuttavia, prima di entrare nel ciclo, è necessario memorizzare i valori dell'intervallo in una nuova variabile ( my_range ) come segue:

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

È necessario specificare in modo esplicito il riferimento alla cella iniziale (F3) e il riferimento alla colonna finale (H). VBA aggiunge automaticamente i valori delle righe per completare l'array di ricerca.

3. Scrivere un ciclo per scorrere ciascun valore di ricerca

Prima di scrivere il ciclo, definire il valore della riga iniziale, 2. La definizione di un valore iniziale per il ciclo è necessaria quando si ha a che fare con componenti dinamici. Nella tabella di destinazione, la riga 2 è la prima riga di dati. Modificare questo valore se i dati iniziano in una riga diversa.

i = 2

Puoi utilizzare un ciclo do- while per elaborare ogni riga, iniziando dalla riga 2 e terminando con la riga 17. Come il resto del codice, questo aspetto è flessibile; Il ciclo verrà eseguito finché la condizione non sarà falsa. Utilizza la condizione per verificare la presenza di un valore non vuoto nella prima cella della riga corrente.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

All'interno del ciclo, puoi chiamare la funzione Vlookup per riempire le celle:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Questi comandi impostano i valori delle celle della riga corrente, rispettivamente nelle colonne 2 e 3. Usano l'oggetto WorksheetFunction per chiamare la funzione Vlookup, passando il valore corrispondente dalla colonna 1 alla ricerca. Passano anche l'intervallo definito inizialmente e l'indice della colonna pertinente per recuperare il valore finale.

Il codice vlookup è pronto, ma devi comunque incrementare la variabile di riga ogni volta nel ciclo:

i = i + 1

Ogni volta che il ciclo viene eseguito, incrementa il valore i di 1 . Ricorda, il valore della riga iniziale è 2, l'incremento si verifica ogni volta che viene eseguito il ciclo. Utilizza la parola chiave loop per impostare la fine di questo blocco di codice.

Quando viene eseguito l'intero codice, popolerà entrambe le colonne con i risultati, in base ai valori nella tabella di origine.

Di seguito l'intero codice di riferimento:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Crea un pulsante per eseguire il codice

Puoi creare un pulsante in una tabella Excel per eseguirlo con un clic. Inserisci la forma desiderata sul foglio Excel vuoto, fai clic destro su di essa e fai clic sull'opzione Assegna macro .

Nella finestra di dialogo, selezionare il nome della subroutine e fare clic su OK .

Quando vuoi eseguire il codice, fai clic sul pulsante per vedere immediatamente come vengono compilati i dati.

Sopra è illustrato come utilizzare Excel VBA per automatizzare le funzioni di ricerca . Spero che l'articolo ti sia utile.