Tienes una enorme tabla de registros contables y necesitas buscar los información que coincida con cierto croterio, un Buscador en Excel es la solución, en este caso mediante el uso de filtro avanzado con VBA, la idea es que al teclear, de manera automática se muestren solo los registros que coinciden con lo tecleado.
Como hacer un buscador en Excel para contactos
En este ejemplo veremos como hacer un buscador en excel mediante vba, esto con el fin de poder hacer búsquedas rápidas en una tabla de contactos.
Tutorial sobre como hacer un buscador en Excel
Actualización 14-11-2023
Ahora cualquier cosa que teclees se buscara en todas las columnas de la tabla.
Definir rango de criterios
Para el filtro avanzado, necesitamos definir un rango de criterios, en este caso el rango sera B2:B3
Para esto hacemos lo siguiente:
- Click en la pestaña “formulas”, en la cinta de opciones de excel
- Click en administrador de nombres
- Click en nuevo
- tecleamos el nombre deseado, en este caso “Criterios”
- Damos click el el botón “se refiere a:” y seleccionamos el rango de celdas
B2:B3 - Aceptar.
Definir rango de datos a filtrar
El rango de datos a filtrar es la tabla que contiene la informacion de los contactos, en este caso también le daremos un nombre como en el paso anterior, pero como esta tabla ira creciendo con los nuevos registros, es importante que el nombre que definamos tome de manera automática esos datos nuevos.
- Click en la pestaña “formulas”, en la cinta de opciones de excel
- Click en administrador de nombres
- Click en nuevo
- tecleamos el nombre deseado, en este caso “Datos”
- En el campo “se refiere a:” ponemos esta formula
=DESREF(Contactos!$A$6,,,CONTARA(Contactos!$A$6:$A$1048576),5) - Aceptar.
Para mas informacion sobre este ultimo caso, puedes ver el post sobre listas y rangos dinámicos que publique hace tiempo.
Código VBA para el filtro avanzado
El siguiente código es el que filtrara los datos cuando se teclee algo en la celda B3
Option Explicit
Sub FiltraDatos()
Range("Datos").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criterios"), Unique:=False
End Sub
Si vemos el código, en este se hace uso de los nombres de rango que se definieron anteriormente.
Programar evento que se ejecutara al momento de cambiar la celda B3
Para que el código anterior se ejecute al hacer un cambio en B3, es necesario programar el evento Change en la hoja de datos.
Para esto damos click derecho en la pestaña con el nombre de la hoja de datos/ ver código, en la ventana que sale ponemos este código.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo casoerror
If Target.Address = "$B$3" And Target.Value <> "" Then
Call FiltraDatos
End If
If Target.Address = "$B$3" And Target.Value = "" Then
ActiveSheet.ShowAllData
End If
Application.ScreenUpdating = False
Exit Sub
casoerror:
Application.ScreenUpdating = False
End Sub
Lo que hace el código anterior es básicamente comprobar si la celda que se esta modificando es B3, en ese caso se ejecuta la función de filtrado, si al modificar la celda B3 se esta borrando el texto, se debe mostrar de nuevo toda la informacion de la tabla, es decir, se elimina el filtro aplicado.
Ejecutar filtro al teclear
Hasta el código que tenemos ahora, el filtro se ejecuta luego de dar enter al estar tecleando en B3, pero también es posible que el filtro se genere automáticamente con cada tecleo en B3, es decir, de ese modo los datos que coinciden se van mostrando conforme se va tecleando.
Para lograr esto, ponemos un cuadro de texto justo encima de B3.
- Click en la ficha “Desarrollador”
- Insertar
- En la seccion de Controles Activex, damos click en Cuadro de texto
- Dibujar cuadro de texto encima de B3
Luego de dibujar el cuadro de texto, dar click derecho sobre el/ ver código y escribimos lo siguiente:
Private Sub TextBox1_Change()
Range("B3") = TextBox1
End Sub
Finalmente vamos a la hoja excel y desactivamos el modo diseño en la ficha “Desarrollador”
Con ese código, cada vez que tecleamos en el cuadro de texto, eso mismo se repetirá en la celda B3, lo cual luego hace que se ejecute el filtro.
Uso de comodín para buscador en excel
Es probable que tengas una gran tabla de datos y no estés seguro de como aparece el dato buscado, pero si recuerdas parte del mimos, para estos casos puedes usar el * justo al inicio del texto buscado, de ese modo se filtraran los registros que contenga ese texto, esto sin importar en que parte de la cadena se encuentra.
Como puedes ver, con muy poco código es posible crear un potente buscador en excel con el uso de filtro avanzado y VBA.
Orlando dice
A la hora de intentar realizar el filtro, me aparece un error “No se ha definido la variable”. Copié el código tal cual:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “B$6$” And Target.Value “” Then
Call FiltraDatos
End If
If Target.Address = “B$6$” And Target.Value = “” Then
ActivateSheet.ShowAllData
End If
End Sub
Abimael Rodriguez M. dice
buenas tardes, mi preguntas
Cuales serian las formulas en excel si lo tengo en ingles?
es que no se cual sea el simil en ingles
saludos
Alexia Gonzalez dice
la formula de desref en ingles es OFFSET, si no me equivoco
Tania dice
Excelente aporte, justo lo que buscaba me sera de mucha utilidad sin duda me convierto en seguidora de esta página.
Gracias por compartir!!
Saludos desde México
cuadro de busqueda en excel dice
Excelente trabajo. Gracias, Un saludo
Mario Herrera dice
Gracias por estos conocimientos MUY UTILES
Un Saludo desde El Salvador.
Julian dice
Hola: Buenos días. Me anda muy bien. Lo unico que me gustaria mejorar es cuando los nombres estan compuestos, es decir nombre y apellido por ejemplo ” Mario Ribotric” si tipeo Ribotric no me lo busca.
Muchas gracias
Miguel dice
Buenas noches, no funciona si reemplazo los nombres por números. Ejm reemplazo un nombre por este 1345-2019 y luego le pongo buscar pero no lo encuentra.
hector dice
tremendo trabajo el que haz realizado.
Mi consulta va en como se podrían agregar columnas / o modificar las que existen para que las incluya en la busqueda?
Sandra Giraldo dice
Hola, como estas.
esta super esta herramienta, quisiera saber como puedo hacer, para tener dos opciones de buscador en la misma hoja, es decir que pueda buscar por un criterio u otro. Como en el ejemplo que pones, pero que pudiera decidir si quiero buscar por nombre o por ciudad por ejemplo.
quedo atenta, mil gracias!
Joel Contreras dice
Buenas tardes Nolberto Lopez,
De antemano agradezco mucho la ayuda que proporcionas con esto me ha sido muy funcional, si de algo sirve… En la celda B2, tiene que escribir el nombre de la columna que van a utilizar para la búsqueda.
Saludos
Alejandro Hernández dice
Excelente aporte, funciona perfectamente no obstante tengo una consulta.
Tengo dos hojas una para Clientes y otra para Productos, he creado un buscador para cada hoja pero en la segunda que he creado con el mismo proceso que la primera me dá un error:
Se ha detectado un nombre ambiguo “FiltrarDatos”.
Sale dentro del código:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo casoerror
If Target.Address = “$G$5” And Target.Value “” Then
Call FiltraDatos
End If
Luis dice
Hola! tengo el problema que al ingresar texto en la TextBox, solo me muestra la primer fila de la tabla… Alguna solucion?
Agustin dice
Me pasa lo mismo, alguna solucion?
Gracias!
Marco dice
yo igual, ya no sé que hacer
Andrés G dice
Buenas lo he realizado todo y me hace el filtro avanzado pero solo muestra la primera fila de la tabla, sea cual sea el criterio a buscar. Podrías orientarme??
Nolberto Lopez dice
Abría que ver su código, mi sugerencia es que descargue el ejemplo de esta pagina.
Alfredo dice
No se ejecuta y me dice error 1004 range de objeto global, como lo soluciono
Nolberto Lopez dice
Saludos Alfredo.
Aquí seria importante que comparta su código y también indicar en que linea le da el error.
jhonathan dice
Excelente aporte, yo lo hice con mas datos. Lo que no encontré es la manera de proteger los datos. Solo dejar visible y editable el botón.
Juan Pablo Humani dice
Hola buenos días como estas todo bien? Estoy intentando utilizar este buscador para un archivo que es un padrón de impuestos de Argentina. Un ejemplo de fila sería:
22082019 1092019 30092019 20000163989 D S N 0,00 0,00 0 0
Lo que quiero es un buscador para ingresar valores que aparecen en la columna 3 y que devuelva el valor de la 7 o sino la fila completa.
Desde ya espero tu respuesta. GRACIAS!!!
Juan Pablo
gregorio dice
Sigo los pasos y no me corre el aplicativo en excel al seleccionar un nombre y dar enter en B3, agradezco el apoyo por favor que me puedas brindar, incluso lo hice con solo tres contactos y nada…
Saludos…
carlos dice
Hola amigo revisa tus variables, si lo estas realizado con el mismo nombre como en el video, podrias enviar tu imagen para que pueda ayudarte
Nolberto Lopez dice
Saludos Carlos.
Sin ver el código que usted escribió no hay manera de saber donde esta el problema.
Evidentemente lo que usted debe hacer ahora es seguir de nuevo todo el proceso.
Otra opción es que descargue el ejemplo.
Mario Medina dice
Hola buenas, estoy intentando hacer el buscador, pero al efectuar el filtro, no filtra lo que estoy escribiendo solo me aparece la primera fila de la bases de datos que tengo, o sea solo el primer dato.
agradeceria si me pudiera ayudar.
Patricia dice
A mi me pasó lo mismo y he visto que a mucha gente también pero ya he descubierto la solución. Me rompí la cabeza analizándolo todo (código, funciones, nombre de hoja, etc) una y otra vez sin ver el video.
El problema no está ni en el código ni en la función DESREF utilizada en la asignación de nombres, sino en un pequeño detalle que se nombra en el video como de pasada y que no está escrito en esta entrada: la celda B2 situada encima del TextBox tiene que coincidir con el título de la columna en la que se hace la búsqueda.
Esta es la clave. Espero haya servido de ayuda a alguien 😉
Tania dice
wow!! genial mil gracias!!!!
marioTabasco dice
¡Excelente aclaración. Saludos!
Marcos dice
Buenos dias, al filtrar no me va mostrarndo los resultados conforma escribo, solo me muestra el resultado concreto al terminar de escribir. Gracias
Oscar molina dice
hola quiero consultar si se puede concatenar el filtrado de varias hojas o todo el libro gracias…
esegovia dice
Gracias por tan útiles tips. Saludos.
LUIS M. HERNANDEZ dice
HOLA, MAGNIFICO Y LO DESCARGUE PERO AL AMPLIAR LOS RENGLONES A MAS DE 4000, SE PONE MUY LENTO Y NO REGRESA AL PRIMER RENGLON.
PODRIAS DECIRME QUE NO ESTOY HACIENDO BIEN? GRACIAS
Ismael dice
Muchas gracias… excelente aporte, se podrá agregar mas de un filtro avanzado en la misma hoja??
Adrian Bonfrisco dice
Buenas.
Muchas gracias por las ayudas en esta pagina y los tips.
Estoy intentando armar el buscador en una lista de precios.
Cuando quiero definir el rango de los datos a filtrar, me tira un error en la formula.
A continuación te dejo mi formula: =desref(Precios!$A$5,,,contara(Precios!$A$5:$A$500),6)
Cuando al error le doy aceptar, me deja pintado esto ” $A$5,,,contara “.
He probado mil cosas, y no logro que me la acepte. Capaz me puede ayudar.
Utilizo Excel 2010.
Muchas gracias desde ya.
Santiago dice
Tienes que utilizar ; en lugar de ,
Hugo Salinas dice
Que tal ingeniero, muy buen aporte, tengo una duda, al realizar el ejemplo me sale este error
Run time Error ‘1004’
Method ‘Range’ of object ‘_Global’ failed
Cree una base de datos propia y tanto la columna de datos como la celda de criterios la llame igual que en tu ejemplo, entonces al insertar el modulo y correr mi programa no funciona y me envía el error señalado
el código es claro el mismo que tu pusiste
Sub FiltraDatos()
Range(“Datos”).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(“Criterios”), Unique:=False
End Sub
Danilo dice
Excelente ejercicio.
Gracias por compartir tus AMPLIOS conocimientos.
Saludos!!!!
Danilo
Danilo dice
Excelente ejercicio.
Como siempre no dejas nada para las dudas.
Danilo