por Hola todos!!
Siento la demora en las actualizaciones del blog, el motivo de todo este tiempo sin publicar nada se debe básicamente a dos motivos:
Primero y más importante, en poco tiempo voy a ser papá y además de ser la mejor noticia de mi vida y sentirme afortunado y feliz, es algo que desde el minuto uno exige plena dedicación. Y en eso me encuentro últimamente.
Segundo, a nivel laboral he estado bastante ocupado con varios proyectos que no han dejado mucho tiempo para dedicárselo al blog.
Pero hoy estoy con tiempo suficiente como para centrarme y poder escribir algo que os resulte interesante y práctico. Hace unos mese realicé una explicación sencilla de cómo trabajar con la función «Buscarv» en excel, de hecho incluí varios post donde además la combinaba con otras funciones como «Coincidir», «Si» o «Eserror». En esta ocasión quiero hablar de la misma función pero no en su uso en la hoja de cálculo como función sino dentro del editor de visual basic.
Os propongo el siguiente ejemplo, imaginad que tenéis una tabla con una serie de personas, todas ellas unidas a un número como identificador único y con el detalle del último título que tiene y si sabe o no inglés, esta es la tabla:
A continuación os entregan otra tabla en la que únicamente aparece una columna con una serie de identificadores desordenados y donde algunos coincidirán o no con los de la primera tabla, y lo que debéis hacer es rellenar las tres columnas siguientes con «Nombre«, «Estudios» e «Inglés«.
Esto lo podéis hacer con una fórmula de forma manual o también en VBA con una macro que automatice el proceso: Esta es la macro que vamos a utilizar:
Sub BUSCARV()
'Seleccionamos la hoja listado y limpiamos las celdas que tengan contenido
Worksheets("LISTADO").Select
limpiardatos = Application.CountA(Worksheets("LISTADO").Range("a:a"))
Sheets("LISTADO").Range("C2:D" & limpiardatos).ClearContents
'Determinamos la longitud del rango de los datos con un contarA
fin = Application.CountA(Worksheets("LISTADO").Range("a:a"))
'Aplicamos la función buscarv para buscar el nombre y si no está que el resultado sea vacío, situamos el resultado en el rango B2 en adelante
With Worksheets("LISTADO").Range("B2:B" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE)),"""",VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE))"
.Formula = .Value
End With
'Aplicamos la función buscarv para buscar los estudios y si no están que el resultado sea vacío, situamos el resultado en el rango C2 en adelante
With Worksheets("LISTADO").Range("C2:C" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-2],DATOS!R2C1:R32C4,3,FALSE)),"""",VLOOKUP(RC[-2],DATOS!R2C1:R32C4,3,FALSE))"
.Formula = .Value
End With
'Aplicamos la función buscarv para buscar si sabe o no inglés y si no está que el resultado sea vacío, situamos el resultado en el rango D2 en adelante
With Worksheets("LISTADO").Range("D2:D" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-3],DATOS!R2C1:R32C4,4,FALSE)),"""",VLOOKUP(RC[-3],DATOS!R2C1:R32C4,4,FALSE))"
.Formula = .Value
End With
'Nombramos el encabezado de cada columna
With Worksheets("LISTADO")
.Range("B1") = ("NOMBRE")
.Range("C1") = ("ESTUDIOS")
.Range("D1") = ("INGLES")
End With
'Coloreamos el encabezado de cada columna (elijo el rojo)
With Worksheets("LISTADO")
.Range("B1").Interior.Color = vbRed
.Range("C1").Interior.Color = vbRed
.Range("D1").Interior.Color = vbRed
End With
End Sub
Como podéis ver no se trata de una macro excesivamente compleja, en verde comento para que sirve cada línea de código y simplemente tenéis que pegar en un nuevo módulo dentro de vuestro proyecto.
A continuación podéis colocar un botón en la hoja «Listado» para ejecutar la macro, de forma que el resultado os quedaría así:
Ahora podéis apreciar la potencia y rapidez de VBA para realizar el trabajo. Realmente es útil para casos estandarizados en los que temporalmente tengáis que actualizar la misma información una y otra vez.
Si todo va como espero, la semana que viene voy a escribir sobre la posibilidad de utilizar en Excel SQL de la misma forma que se utiliza en Access, estoy seguro que os encantará.
Y ahora, como siempre os dejo el archivo para podáis tener el ejemplo práctico.
Descarga el archivo de ejemplo pulsando en: BUSCARV EN VBA
Muchas gracias amigo y muchas felicidades en esta nueva etapa. Un salud!
hola muy buenas noches de veras aprecio y te felicito por tan excelente aporte , tengo una duda con respecto a esta macro
supongamos que el identificador no quiero que este en la columna a si no en la e por ejemplo ¡donde tendria que inducarle que me tome esa columna he estado examinando pero no logro saber donde creo que tiene que ver con esta linea
fin = Application.CountA(Worksheets("LISTADO").Range("a:a"))pero la cambio y no logro obtener resultados
a espera de tus comentarios
saludos
Hola Abel,
Y estás en lo cierto, supongamos que en el ejemplo, en la pestaña «listados» tenemos los datos que queremos buscar en la columna «E», para hacer la prueba puedes copia y pagar los datos de la columna A en la E y la fórmula sería esta:
With Worksheets("LISTADO").Range("F2:F" & fin).Formula = "=IF(ISERROR(VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE)),"""",VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE))"
.Formula = .Value
End With
Como puedes ver, hacemos referencia a la columna «F» (que es donde vamos a insertar el resultado de la búsqueda), dado que la fórmula buscaremos en la columna «E», determinamos con VLOOKUP(RC[-1] donde [-1] quiere decir que se busque en la columna anterior, (donde tenemos los datos).
Gracias por comentar y participar.
RC[-1] Que estamos diciendo con esto?, entiendo es el valor buscado pero porque se representa así
(«C2:D» & limpiardatos)
Aqui porque es la D?, entiendo se borra antes de buscar hasta la columna c fila 2 y hasta todas las filas que conto anteriormente en la columna A
Por su respuesta y aporte muchas gracias
Hola Fabián:
Debes familiarizarte previamente a la notación R1C1 de Excel para luego programar el buscarv de esta forma.
Utiliza el buscador de esta web y pon buscarv. encontrarás varias entradas con una programación más amigable.
RC[-1] es una columna a la izquierda de la selección en la que estás.
Saludos
Que Maravilla, mil gracias por la valiosisima ayuda, me sirvio demasiado!!!
Gracias Andrés.
También tienes en esta entrada otra forma de realizar buscarv en VBA, sin usar la notación de estilo R1C1.
https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
Saludos
Hola, muchas gracias por el aporte, es justo lo que necesitaba!! Tengo un problema parecido al de Abel, el identificador lo modificaría a la columna E y añadiría un fila en la parte superior, empezando a trabajar la macro en la fila 3. El código sería este:
Sub BUSCARV()'Seleccionamos la hoja listado y limpiamos las celdas que tengan contenido
Worksheets("LISTADO").Select
limpiardatos = Application.CountA(Worksheets("LISTADO").Range("a:a"))
Sheets("LISTADO").Range("F2:H" & limpiardatos).ClearContents
'Determinamos la longitud del rango de los datos con un contarA
fin = Application.CountA(Worksheets("LISTADO").Range("a:a"))
'Aplicamos la función buscarv para buscar el nombre y si no está que el resultado sea vacío, situamos el resultado en el rango
'B2 en adelante
With Worksheets("LISTADO").Range("F3:F" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE)),"""",VLOOKUP(RC[-1],DATOS!R2C1:R32C4,2,FALSE))"
.Formula = .Value
End With
'Aplicamos la función buscarv para buscar los estudios y si no están que el resultado sea vacío, situamos el resultado en el rango
'C2 en adelante
With Worksheets("LISTADO").Range("G3:G" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-2],DATOS!R2C1:R32C4,3,FALSE)),"""",VLOOKUP(RC[-2],DATOS!R2C1:R32C4,3,FALSE))"
.Formula = .Value
End With
'Aplicamos la función buscarv para buscar si sabe o no inglés y si no está que el resultado sea vacío, situamos el resultado en el rango
'D2 en adelante
With Worksheets("LISTADO").Range("H3:H" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-3],DATOS!R2C1:R32C4,4,FALSE)),"""",VLOOKUP(RC[-3],DATOS!R2C1:R32C4,4,FALSE))"
.Formula = .Value
End With
'Nombramos el encabezado de cada columna
With Worksheets("LISTADO")
.Range("F2") = ("NOMBRE")
.Range("G2") = ("ESTUDIOS")
.Range("H2") = ("INGLES")
End With
'Coloreamos el encabezado de cada columna (elijo el rojo)
With Worksheets("LISTADO")
.Range("F2").Interior.Color = vbRed
.Range("G2").Interior.Color = vbRed
.Range("G2").Interior.Color = vbRed
End With
End Sub
El problema es que me da error en limpiar dato:
limpiardatos = Application.CountA(Worksheets("LISTADO").Range("a:a"))Sheets("LISTADO").Range("F2:H" & limpiardatos).ClearContents
¿Cual sería el error? mil gracias por adelantado
Hola Fco.
Envía el archivo que tienes. Con la misma estructura y datos inventados, así puedo ver cual es el problema. excelsignum@yahoo.es
Saludos
Te lo envío ahora mismo, muchas gracias por tu rápida respuesta
Hola Francisco:
Ya he visto el archivo:
Debes modificar la variable «limpiardatos», el rango es la columna «E» y si la primera fila está en blanco debes sumarle +1
limpiardatos = Application.CountA(Worksheets("LISTADO").Range("E:E")) + 1Por otra parte, también debes modificar en la macro la variable «fin», dado que los datos empiezan en la columna «E». y como tienes una fila en blanco al principio, debes sumarle +1
fin = Application.CountA(Worksheets("LISTADO").Range("E:E")) + 1Si incluyes estos cambios en la macro, te funcionará perfectamente.
Saludos.
Hola Segu,
La macro modificada funciona perfectamente con las correcciones que indicas.
Muchas gracias por tu ayuda, ademas de la gran calidad de las entradas de la web, cuando se te pide ayuda con adaptaciones que hacemos das un soporte exquisito!!
Son ya varios años los que llevo leyendo tu web y espero que puedan seguir siendo muchos más.
De nuevo muchas gracias.
Muchas gracias por tus palabras Francisco. Un abrazo!
Geniooooo…!!!!!!!!!!! mil gracias por tu aporte, una consulta, como puedo hacer exactamente lo mismo, pero teniendo los datos en otro libro?
hola, cual seria el caso que la opción buscar fuera en otro libro Excel.
Hola Jorge:
Siguiendo el ejemplo, imagina que tienes que buscar la información en un nuevo archivo que se denomina Libro1 y los datos están en la DATOS, esta sería la fórmula, debes especificar la información con [ ], siempre que estés en el mismo directorio,
"=VLOOKUP(RC[-1],[Libro1.xlsx]DATOS!R2C1:R31C4,1,0)"En caso de no ser así, debes especificar el string hasta llegar al directorio, ej:
=VLOOKUP(RC[-1],'C:\Users\[Libro1.xlsx]DATOS'!R2C1:R31C4,1,0)"No obstante, hay otras formas de hacer búsquedas e incluso otras formas de programar buscarv más eficientes:
https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
https://excelsignum.com/2016/04/21/buscar-con-varios-criterios-en-vba/
Hola, quiero felicitarte por la dicha de que sientas la felicidad de ser padre, a pesar de que éste blog tiene tiempo de ser publicado, no quise perderme la oportunidad de desearte lo mejor a ti y a tu bebé.
Después de eso quiero agradecer mucho tu aporte, a mí me funcionó de maravilla así como tu explicación.
Un saludo.
Muchas gracias por tus palabras Isabel, sí la peque ya tiene dos años y unos meses y está estupenda!. Me alegro que el post haya sido útil. Muchas gracias!
Hola, mil felicitaciones. Gracias por ese gran aporte, muy útil, pero quiero preguntarte…si quiero que la columna identificador de datos sea la columna c, como modifico la formula. Gracias y nuevamente felicitaciones!
Hola David:
Me alegra que te resulte de interés y de utilidad. Sobre tu consulta, te falta decirme a que identificador te refieres, el de la hoja DATOS o el de la hoja LISTADO?. Dependiente de si es uno y otro, la fórmula se debe modificar de manera diferente.
Saludos.
Hola Segu.
Recien aparezco por aquí y me ha gustado mucho como enseñas.
Por lo mismo quiero pedirte una recomendación, quiero aprender bien las bases del VBA en excel. Para ello debe haber un manual o una ruta a seguir que quiero tu me recomiendes.
Te lo agradeceria enormemente.
Patricio.
Hola Patricio:
Para aprender a programar con VBA, al igual que otros conocimientos lo más recomendable es que compagines la lectura de manuales:
por ejemplo, este es con el que comencé:
https://www.casadellibro.com/libro-excel-2010-visual-basic-para-aplicaciones/9788441528734/1829536
Con la consulta en foros y web especializadas.
Debes tener en cuenta que es un proceso y por lo tanto, lo irás aprendiendo poco a poco al mismo tiempo que lo irás perfeccionando.
Elige tareas que habitualmente realices e intenta automatizarlas, primero usando la grabadora de macro y luego modificando y personalizando el código.
En resumen, desarrollo en base a publicaciones y consultas y la práctica constante.
Saludos.
Hola buenas sabes tengo un problema , tengo que buscar en una planilla el rut de una persona en base a su nombre en un inputbox , y que dicho resultado me aparezca como un msgbox
Hola Bri:
No necesitas usar buscarv, con un bucle for-next puedes solucionarlo.
Tienes ejemplos en esta web de cómo programar este tipo de loop. Una vez que encuentres el resultado solo tienes que guardarlo en una variable y mostrarlo en el msgbox.
De todas formas no puede decirte mucho más, dado que desconozco los datos.
Saludos.
Hola Segu.
Te agradezco mucho el tiempo que dedicas a compartir tus conocimientos. Es excelente tu blog.
He podido aplicar tu código perfectamente siempre y cuando tipee los datos de la columna «A» (o sea los datos a buscar). Pero en mi caso, ese dato es un número de 9 dígitos, y son unas 300 líneas aproximadamente. Sería más fácil para mi «copiar y pegar» el dato a buscar, pero si hago eso no obtengo resultados. Si borro el último dígito y lo vuelvo a escribir funciona. No se en qué me estoy equivocando.
Saludos cordiales desde el norte de la Patagonia Argentina.
Hola Sergio:
Debería funcionarte independientemente de los dígitos, simplemente es el buscarv pero programado. No obstante puedes ver en este post otra forma de programar el buscarv: https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
Saludos.
Y si no quiero un botón para ejecutar la macro. Es decir que sea automático. Es posible?
Hola Antonio: Sí, claro, lo puedes vincular a un evento de hoja, tipo tras realizar dobleclick, seleccionar una celda, etc.
Saludos.
Master un saludo,
tome tu condigo para generar una categorizacion de cuentas, sin embargo cada que actualizo la base de datos me dejo celadas sin limpiar y sin aplicar el buscador.
el codigo es el siguiente :
Sub Buscadores()'Seleccionamos la hoja listado y limpiamos las celdas que tengan contenido
Worksheets("Detalle FCx+ACT").Select
limpiardatos = Application.CountA(Worksheets("Detalle FCx+ACT").Range("g:g"))
Sheets("Detalle FCx+ACT").Range("X26:X" & limpiardatos).ClearContents
'Determinamos la longitud del rango de los datos con un contarA
fin = Application.CountA(Worksheets("Detalle FCx+ACT").Range("g:g"))
'Aplicamos la función buscarv para buscar el nombre y si no está que el resultado sea vacío, situamos el resultado en el rango B2 en adelante
With Worksheets("Detalle FCx+ACT").Range("X26:X" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-13],TD!R65C7:R171C9,3,FALSE)),"""",VLOOKUP(RC[-13],TD!R65C7:R171C9,3,FALSE))"
.Formula = .value
End With
'Nombramos el encabezado de cada columna
With Worksheets("Detalle FCx+ACT")
.Range("X25") = ("Grupo de Cuentas")
End With
'Coloreamos el encabezado de cada columna (elijo el rojo)
With Worksheets("Detalle FCx+ACT")
.Range("X25").Interior.Color = vbRed
End With
ThisWorkbook.RefreshAll
End Sub
Espero me puedas apoyar
Necesitaría ver tu macro en un archivo y con un ejemplo. Pero así, según tu código, lo que se borra es de la columna X y fila 26 en adelante según el número que tenga la variable limpiar datos.
Recuerda que esta variable tiene el valor de lo que cuente en la columna G.
Verifica esta información, saludos
Hola buenas noches me gustaría ayuda con otra formula que me funcionan en Excel pero que necesito el comando o función para VBA.
El formula es la siguiente: “=SI(ESERROR(BUSCARV(BY8,AZ:BA,4,FALSO)),””,BUSCARV(BY8,AZ:BA,4,FALSO))”
Lo que realiza es que busca lo que se encuentra en la celda BY8 y traer lo que contiene la 4 columna a la derecha.
Lo que necesito es que haga lo mismo y que el resultado lo ponga en un TextBox.
Por su ayuda de antemano gracias.
Saludos
Hola Daniel:
En este post tienes otra forma de programar la función: https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
No es necesario que utilices si (eserror, debes usar si.error. En el post verás cómo se programa.
Excelente. Muchas gracias por compartir conocimiento.
Buenas tardes.
Y si quisiera copiar el formato de la celda origen a la celda destino ¿cómo sería? ¿Me podéis ayudar con este detalle?
Muchas gracias
Hola Carlos:
La función buscarv no trae el formato. Para eso debe programar el formato en el momento en que la función deja el resultado en la celda. Saludos.
muchas gracias por la respuesta. Aquí tengo dos problemas:
* En la rutina que pones, con el With, se completan todas las celdas al mismo tiempo por lo que se me hace complicado controlar cuando se deja el resultado para cada una de ellas.
* No encuentro la propiedad exacta o función que se corresponde con el pincel de copiar formato. No soy capaz de copiar el formato de una celda a otra y he buscado durante todo el día de ayer hasta la saciedad.
Si me puedes ayudar en estas dos cosas te lo agradecería.
Muchas gracias.
Hola Carlos:
En la web hay varios ejemplos de la programación con Buscarv. La que estás utilizando es la de notación RC, quizás un poco más compleja a la hora de programar.
En este post tienes el ejemplo de cómo hacerlo con la función:
https://excelsignum.com/2019/03/02/programar-buscarv-en-vba/
Y además verás como en el archivo de destino formateo de color rojo encabezados. Esto te servirá de ejemplo.
Saludos.
Gracias por contestar.
No tengo ningún problema en dar formato a una celda. El problema lo encuentro es que la función «=IF(ISERROR(VLOOKUP(RC[-13],TD!R65C7:R171C9,3,FALSE)),»»»»,VLOOKUP(RC[-13],TD!R65C7:R171C9,3,FALSE))» hace un barrido en una única sentencia de todas las celdas que tiene que copiar y que se corresponden con las filas encontradas.
El problema es que en esta sentencia no puedo intercalar, para las celdas encontradas, ninguna sentencia para dar formato y así poderle dar formato. No se si me explico.
No se si me explico.
Al final lo he resuelto con un par de For. Lo único es que tarda una inmensidad para una Excel con 1000 filas y además, resulta algo rudimentario ¿no?
Algo así:
‘recorro la hoja destino
For fila1 = 1 To fin
codigo_1 = Worksheets(«Fecha1»).Cells(fila1, 1)
‘recorro la hoja origen
For fila2 = 1 To final
codigo_2 = Worksheets(«Fecha2»).Cells(fila2, 1)
Worksheets(«Fecha2»).Cells(fila1, 14).Value = Worksheets(«Fecha1»).Cells(fila2, 14).Value
Sheets(«Fecha2»).Select
Range(«L» & fila2, «Q» & fila2).Select
Selection.Copy
Sheets(«Fecha1»).Select
Range(«L» & fila1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Exit For ‘ salgo del bucle porque ya la he encontrado
End If
Next fila2
Next fila1
Hola Carlos:
No te pegué el post correcto. Disculpa:
https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
Es este, en el que se usa una instrucción for-next. aquí puedes incluir un condicional para detectar y formatear cualquier caso.
https://excelsignum.com/2016/07/26/utilizar-cuadro-de-dialogo-para-seleccionar-archivo-y-buscarv/
Utilicé un cuadro de diálogo para el ejemplo, pero la dinámica es con un condicional:
if .Cells(i, 2)= «tu ejemplo» then formatea
Con el otro método ganas en rapidez pero no te permite seleccionar el caso, eso lo pemiten los bucles porque recorremos la columna o la fila.
Saludos
Hola Segu, como estás.
Te escribo desde Chile
Alguna posibilidad de solicitar una ayuda, cuya explicación sea vía mail.
Desde ya agradecido
Un Abrazo
Hola Gregorio: el mail es info@excelsignum.com