Cómo automatizar Vlookup usando Excel VBA

Vlookup es una función esencial en Excel y se ha convertido en una parte importante del procesamiento de datos. Proporciona una serie de funciones que puede combinar con una base de datos completa.

Sin embargo, si desea automatizar esta función, puede hacerlo, especialmente si sabe utilizar el lenguaje de automatización disponible en Excel y VBA. A continuación se explica cómo puede automatizar la función vlookup en Excel VBA .

Fórmula de la función Vlookup en Excel

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

Significado de los argumentos en la fórmula:

• lookup_value: El valor subyacente que desea buscar en la matriz.
• lookup_array: Datos de origen, el valor_buscado se utilizará como referencia.
• column_index: La columna devuelve valor.
• exact_match/partial_match: Utilice 0 o 1 para especificar el tipo de combinación.

Función VBA Vlookup en Excel

El uso de la función Vlookup no es muy diferente cuando la usa directamente en Excel o mediante VBA. Ya sea que un analista de datos procese filas de información o un gerente de almacén trabaje constantemente con nuevos productos, la función vlookup es útil.

Cuando se trabaja con un rango de búsqueda dinámico, lo mejor es automatizar la fórmula para evitar tener que manipular la misma fórmula varias veces en Excel. Al automatizar la función Vlookup en VBA, puede realizar cálculos de varias columnas con un solo clic.

Ilustración

Este conjunto de datos tiene dos partes: una tabla de búsqueda y puntos de datos de destino. La tabla de búsqueda contiene 3 columnas, con datos en los dos campos Subcategoría y Nombre del producto:

La tabla de destino tiene las columnas adecuadas, sin subcarpetas ni datos de nombres de productos. Tenga en cuenta que los valores de la columna ID de pedido también aparecen en la misma columna en la tabla de búsqueda:

1. Realice los ajustes necesarios

Comience abriendo el editor de código (presione Alt + F11 o navegue hasta la pestaña Desarrollador ) en un nuevo libro de Excel y agregue el módulo para comenzar a codificar. En el editor de código, agregue las siguientes líneas en la parte superior de la ventana de programación para crear una subrutina:

Sub vlookup_fn1()End Sub

La subrutina es un contenedor para el código VBA y es necesaria para ejecutarlo correctamente. Otra alternativa es crear un formulario de usuario VBA para que la interfaz de usuario sea más interactiva.

2. Declarar variables y crear ámbitos de referencia.

Primero, necesitas declarar los tipos de datos de las variables usando el comando Dim:

Dim i as integer, lastrow as long

A continuación, cree una variable lastrow para almacenar el valor de la última fila poblada en el rango de búsqueda. La función lastrow utiliza la función end(xldown) para calcular la referencia de la última fila en un rango específico.

En este ejemplo, la variable lastrow contiene el valor de la última fila completa en el rango de búsqueda, específicamente 17.

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

En el ejemplo anterior, la tabla de destino se extiende desde A2:C17 y la tabla de origen se extiende desde F2:H17. La fórmula vlookup recorrerá cada valor en la columna A, lo buscará en la tabla fuente y pegará las entradas combinadas en las columnas B y C.

Sin embargo, antes de ingresar al bucle, debes almacenar los valores del rango en una nueva variable ( my_range ) de la siguiente manera:

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

Debe especificar explícitamente la referencia de celda inicial (F3) y la referencia de columna final (H). VBA agrega automáticamente valores de fila para completar la matriz de búsqueda.

3. Escriba un bucle para recorrer cada valor de búsqueda.

Antes de escribir el bucle, defina el valor de la fila inicial. 2. Definir un valor inicial para el bucle es necesario cuando se trata de componentes dinámicos. En la tabla de destino, la fila 2 es la primera fila de datos. Cambie este valor si los datos comienzan en una fila diferente.

i = 2

Puede utilizar un bucle do- while para procesar cada fila, comenzando en la fila 2 y terminando en la fila 17. Como el resto del código, este aspecto es flexible; El bucle se ejecutará hasta que la condición sea falsa. Utilice la condición para probar un valor que no esté vacío en la primera celda de la fila actual.

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

Dentro del bucle, puedes llamar a la función Vlookup para llenar las celdas:

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)

Estos comandos establecen los valores de las celdas de la fila actual, en las columnas 2 y 3 respectivamente, utilizan el objeto WorksheetFunction para llamar a la función Vlookup, pasando el valor correspondiente de la columna 1 a buscar. También pasan el rango que definiste inicialmente y el índice de la columna relevante para obtener el valor final.

El código de vlookup está listo, pero aún necesita incrementar la variable de fila cada vez en el bucle:

i = i + 1

Cada vez que se ejecuta el ciclo, incrementa el valor i en 1 . Recuerde, el valor de la fila inicial es 2, el incremento se produce cada vez que se ejecuta el ciclo. Utilice la palabra clave loop para establecer el final de este bloque de código.

Cuando se ejecuta el código completo, se completarán ambas columnas con resultados, según los valores de la tabla fuente.

A continuación se muestra el código de referencia completo:

 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 botón para ejecutar el código.

Puede crear un botón en una tabla de Excel para ejecutarlo con un solo clic. Inserte la forma deseada en la hoja de Excel en blanco, haga clic derecho sobre ella y haga clic en la opción Asignar macro .

En el cuadro de diálogo, seleccione el nombre de la subrutina y haga clic en Aceptar .

Cuando quieras ejecutar el código, haz clic en el botón para ver cómo los datos se completan de inmediato.

Arriba se explica cómo utilizar Excel VBA para automatizar funciones de búsqueda . Espero que el artículo te sea útil.