por Hoy vamos a ver otra forma de realizar una búsqueda con la función buscarv en VBA (fruto de una petición que me trasladó un lector).
Imaginad que tenéis una serie de ID y que cada mes necesitáis actualizar cierta información buscando datos de una carpeta donde se va generando un archivo por cada mes. Esta tarea se podría realizar de varias formas, tan solo tenemos que introducir una fórmula o proceso de búsqueda y detallar el archivo en donde debe buscar.
Sin embargo, esto requeriría modificar la cadena de conexión cada vez que necesitásemos actualizar los datos, puesto que estaría en otro archivo, o carpeta o directorio. Para salvar este problema he implementado una serie de modificaciones en una macro que solucionará este problema.
Como íbamos comentando, tenemos una serie de ID en un archivo que actualizamos cada mes:
Y tenemos una carpeta con varios archivos denominados con una fecha, cada uno se corresponde a un mes. Por ejemplo, vamos a actualizar nuestro archivo con los datos del archivo “01_01_2016”, los datos son estos y están en situados en la primera hoja:
Ahora en el archivo con el que estamos trabajando y actualizando la información, incluimos esta macro en un módulo estándar:
Sub Buscarv()
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Definimos variables
Dim i As Double, Libro As Variant, Hoja As Variant
'Asignamos Milibro como thisworkbook
Set Milibro = ThisWorkbook
'Con mi libro en la hoja1 limpiamos el rango de datos que vamos a buscar
With Sheets(1)
fin = Application.CountA(.Range("A:A"))
.Range("B2:D" & fin).ClearContents
'Abrimos cuadro de diálogo para elegir archivo donde buscar
Archivo = Application.GetOpenFilename
'Si no seleccionamos nada salimos del proceso
If Archivo = False Then
Exit Sub
End If
'Asignamos BaseBuscar como el libro abierto
Set BaseBuscar = Workbooks.Open(Archivo)
'Obtenemos el nombre del libro
Libro = ActiveWorkbook.Name
'Obtenemos el nombre de la hoja
Hoja = Sheets(1).Name
'Volvemos a activar el libro en uso
Milibro.Activate
'Iniciamos un bucle y buscamos el valor de la primera columna de nuestro libro
'en el libro que hemos seleccionado y la hoja(1)
For i = 2 To fin
.Cells(i, 2) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0), "")
.Cells(i, 3) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0), "")
.Cells(i, 4) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0), "")
Next i
End With
'Cerramos el libro que hemos abierto para buscar
BaseBuscar.Close
End Sub
Como podéis apreciar lo primero que vais a ver es como Excel abre la ventana de diálogo de “Abrir Archivo”, es ahí cuando debéis seleccionar el archivo desde el cual queréis actualizar la información, en este caso “01_01_2016”:
Ahora la macro detectará la ruta del archivo (ubicación), nombre del libro y nombre de la hoja, datos suficientes para aplicar la función Buscarv. (los pasos los comento en la macro):
Y este es el resultado final:
Y para el siguiente mes, hacemos exactamente lo mismo, buscamos en la carpeta y actualizamos. De esta forma no tenemos que estar modificando el código, Excel lo hace por nosotros.
Es importante tener en cuenta que para este ejemplo, los datos a buscar están en la «hoja1», sin embargo podéis especificar la hoja en la que se buscar modificando en la macro la siguiente línea Hoja = Sheets(1).Name
Hoja = Activesheet.Name o Sheets("el nombre de la hoja").Name
* Para versiones en 2003 es necesario que modifiquéis la línea de código de buscarv, en concreto se debe a la función Application.WorksheetFunction.IfError dado que aparece en Excel a partir de la versión 2007. Para que pueda funcionar en 2003 es necesario sustituirla por un condicional y la fórmula eserror (iserror). Es decir debería estar así:
For i = 2 To fin
'Primer buscarv
If IsError(.Cells(i, 2) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0)) Then
.Cells(i, 2) = ""
Else
.Cells(i, 2) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 2, 0)
End If
'Segundo buscarv
If IsError(.Cells(i, 3) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0)) Then
.Cells(i, 3) = ""
Else
.Cells(i, 3) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 3, 0)
End If
'Tercer buscarv
If IsError(.Cells(i, 4) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0)) Then
.Cells(i, 4) = ""
Else
.Cells(i, 4) = Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0)
End If
Next i
Tan solo tenéis que modificar los datos y os funcionará perfectamente en todas las versiones. Para este ejemplo, lo dejo con la función .IfError, que además reduce considerablemente el código.
Para finalizar os dejo dos archivos, uno contiene la macro y el otro el archivo con la fecha, que contiene los datos a buscar, tan solo tenéis que descargarlos y realizar la prueba 🙂
Espero que os resulte de utilidad!
Descarga el archivo de ejemplo pulsando en: UTILIZAR CUADRO DE DIALOGO PARA SELECCIONAR ARCHIVO Y BUSCARV
Descarga el archivo de ejemplo pulsando en: ARCHIVO DE BÚSQUEDA 01/01/2016
Buenos días.
En primer lugar quiero felicitarte por el foro. Las entradas que he leido siempre las he encontrado mas que interesantes.
He descargado los dos archivos de ejemplo y en mi caso, no funciona.
El programa deja de funcionar en la linea:
«.Cells(i, 2) = Application.WorksheetFunction.IfError(Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range(«A2:E65000»), 2, 0), «»)» y da el error número 438 «El objeto no admite esta propiedad o método» .
Saludos.
Hola Vicente,
Qué versión de Excel tienes?. De todas formas te he enviado un correo con una posible solución. Si va Ok publicaré una nota para posibles incidencias.
Saludos.
— Editado–
Después de haber comprobado el problema, resulta que el archivo estaba en Excel 2003, con lo que la función introducida con buscarv (iferror) propia de 2007 y versiones superiores es la que ocasionaba el error.
Aunque sea una versión antigua, me gusta siempre que puedo que todo el código que escribo se pueda utilizar en todas las versiones, de modo que he actualizado la entrada proporcionando una solución para que funcione en 2003.
Saludos.
genioo, una pregunta, y si el libro de donde sacare los datos esta en la misma ubicacion del libro que lo tengo abierto? que cambio? siempre estara alli, no quiero que me haga buscar otra vez el libro, xfa ayuda
Hola Brayan.
En estos momentos estoy de vacaciones de Navidad. Trataré de responderte lo antes posible. Pero no dispongo de ordenador en este momento.
Únicamente tienes que indicar la ruta del archivo en la función buscar y eliminar la parte de la macro que busca el archivo.
Hola muy buen libro, tengo una duda. Si en lugar de obtener información de filas busco que traspase la información de un rango determinado, que seria lo que tengo que modificar?
Hola:
Esta macro no está pensada para hacer lo que indicas. Creo que este post te puede ayudar:
https://excelsignum.com/2017/03/28/agrupar-informacion-de-varios-libros-en-una-hoja-excel/
Saludos.
Buenas tardes, .
En primer lugar felicitarte por tu blog, es realmente interesante.
Quería preguntarte, en el caso de necesitar en una celda hacer la suma de dos o mas búsquedas en libros diferentes, de que modo puedo indicarle al código como hacerlo.
El problema en concreto es que debo buscar un código, en dos o mas libros diferentes, sumar los resultados y colocarlo en la hoja donde se encuentra el código buscado, no si podrías darme alguna pauta para poder hacerlo.
Un saludo y gracias
Tienes que utilizar un cuadro de diálogo que te permita seleccionar varios archivos, luego a través de un loop recorrer esos archivos y extraer los datos que necesitas.
Es parecido a las programación que tengo en esta web sobre consolidar archivos.
Saludos.
Buenas tardes de nuevo,
Muchas gracias por contestar tan rápido a mi duda.
He revisado lo referente a consolidar archivos, pero en mi caso no se como poder acceder en cada caso a los distintos libros para buscar datos que no siempre son los mismos.
Muchas gracias
En cuanto tenga un momento intentantaré programar un ejemplo. Saludos
Intente implementar el código para buscar en un archivo con mas columnas, pero, solo me busca los datos hasta la tercera, no se si me explico
Hola Luis:
Claro, es que solo está programado para buscar hasta la 3 columna. Fíjate en la programación e intenta replicar el código para una columna más.
Saludos
Hola buen día, si quiero que busque en una columna distinta, que tendría que hacer?.
Agradezco de antemano
Lo indicas en la función de la misma forma que lo haces en Excel.
Application.VLookup(.Cells(i, 1), Workbooks(Libro).Sheets(Hoja).Range("A2:E65000"), 4, 0))Si deseas buscar en la columna 5, indicas un 5 en lugar de un 4 y verificar que estás dentro del rango.
Saludos
Gracias por la anterior respuesta, me refiero a que la columna de donde se toma el valor de referencia, en este caso el ID, estuviera en la columna B o en cualquier otra.
En esta parte de la fórmula buscarv:
Application.VLookup(.Cells(i, 1),
Cuando se indica cells, sus parámetros son (fila y columna), por lo tanto, si la columna ID es la 1 (que es lo que tiene esta fórmula), y quieres la columna B, que es la dos, debes poner .Cells(i, 2)