Ejercicio práctico 2: Cómo buscar un valor en una hoja Excel con VBA.

buscar-valor-excel

Escrito por Administrador

1 de septiembre de 2021

En este ejercicio, vamos a buscar un valor en una hoja Excel con un listado de nombres y apellidos usando VBA. De esta forma, conforme vayamos rellenando campos en la búsqueda, van a marcarse de forma automática los campos coincidentes en el listado. Por lo tanto, cuando completemos el nombre de la búsqueda, solo quedará marcada en el listado esa persona.

En la figura se muestra el listado con el que partimos, con los campos “Nombre”, “1º Apellido” y “2º Apellido”. Además, a la derecha, aparecen los mismos campos para realizar la búsqueda.

total-operaciones-matematicas-macros

En primer lugar, vamos a ver el funcionamiento general. Primero, empezamos rellenando el campo “Nombre” en la búsqueda, y si el valor introducido coincide con alguno del listado, se marcará en rojo.

total-operaciones-matematicas-macros

Así mismo, introducimos el campo “1º Apellido” en la búsqueda. Si los campos “Nombre” y “1º Apellido” de la búsqueda coinciden con alguno del listado, se marcarán en rojo en el mismo.

total-operaciones-matematicas-macros

Por último, introducimos el campo “2º Apellido” en la búsqueda y si los campos “Nombre”, “1º Apellido” y “2º Apellido” de la búsqueda coinciden con alguno del listado, se marcarán en rojo en el mismo.

total-operaciones-matematicas-macros

A continuación, dejo el código para buscar un valor en una hoja Excel con un listado de nombres y apellidos usando VBA.

Macro 1. Colorear campos coincidentes conforme se rellena una celda.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Macro que al ir rellenando el nombre de la persona, va marcando en rojo los campos coincidentes

'Guardamos en variables el nombre y apellidos introducidos en las celdas

nombre = Cells(9, "H")

primerapellido = Cells(9, "I")

segundoapellido = Cells(9, "J")

'Si el valor del nombre cambia

If Not Application.Intersect(Range("H9:H9"), Target) Is Nothing Then

'Borra el contenido de las celdas Apellidos

Cells(9, "I").ClearContents

Cells(9, "J").ClearContents

'Quita el color de celda de las columnas nombre y apellidos

Range("B2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("B2:D92").Interior.Pattern = xlNone

Range("B2:D92").Interior.TintAndShade = 0

Range("B2:D92").Interior.PatternTintAndShade = 0

'Guardamos en la variable nombre el valor introducido en la celda

nombre = Cells(9, "H")

'Marca en rojo los nombres del listado coincidentes con el contenido de la variable nombre

For i = 2 To 92

If Cells(i, "B") = nombre Then

Cells(i, "B").Interior.ColorIndex = 3

End If

Next

End If

'Si el valor del primer apellido cambia

If Not Application.Intersect(Range("I9:I9"), Target) Is Nothing Then

'Borra el contenido de la celda del Segundo Apellido

Cells(9, "J").ClearContents

'Quita el color de celda de las columnas apellidos

Range("C2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("C2:D92").Interior.Pattern = xlNone

Range("C2:D92").Interior.TintAndShade = 0

Range("C2:D92").Interior.PatternTintAndShade = 0

'Guardamos en la variable primerapellido el valor introducido en la celda

primerapellido = Cells(9, "I")

'Marca en rojo los nombres y primeros apellidos del listado coincidentes con el contenido de las variables nombre y primerapellido

For i = 2 To 92

If Cells(i, "B") = nombre And Cells(i, "C") = primerapellido Then

Cells(i, "C").Interior.ColorIndex = 3

End If

Next

End If

'Si el valor del segundo apellido cambia

If Not Application.Intersect(Range("J9:J9"), Target) Is Nothing Then

'Quita el color de celda de la columna del segundo apellido

Range("D2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("D2:D92").Interior.Pattern = xlNone

Range("D2:D92").Interior.TintAndShade = 0

Range("D2:D92").Interior.PatternTintAndShade = 0

'Guardamos en la variable segundoapellido el valor introducido en la celda

segundoapellido = Cells(9, "J")

'Marca en rojo los nombres y apellidos del listado coincidentes con el contenido de las variables nombre, primerapellido y segundoapellido

For i = 2 To 92

If Cells(i, "B") = nombre And Cells(i, "C") = primerapellido And Cells(i, "D") = segundoapellido Then

Cells(i, "D").Interior.ColorIndex = 3

End If

Next

End If

End Sub

Sobre todo, hay que tener claro que el código se va a ejecutar en tiempo real, es decir, conforme se vayan actualizando las celdas de la búsqueda. Por ello, no vamos a crear un módulo en esta ocasión (esto lo haríamos si utilizáramos un botón para ejecutar la búsqueda), si no que el código irá escrito directamente en la hoja usando el método Intersect.

En primer lugar, comenzamos guardando en variables los nombres introducidos en los campos de búsqueda.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Macro que al ir rellenando el nombre de la persona, va marcando en rojo los campos coincidentes

'Guardamos en variables el nombre y apellidos introducidos en las celdas

nombre = Cells(9, "H")

primerapellido = Cells(9, "I")

segundoapellido = Cells(9, "J")

A continuación, vigilamos el cambio del campo “Nombre” de la búsqueda con el comando Intersec. Si cambia dicha celda, borra el contenido de los campos “1º Apellido” y “2º Apellido” de la búsqueda y establece el blanco de fondo en las celdas del listado por si hubiera alguna marcada de búsquedas anteriores.

'Si el valor del nombre cambia

If Not Application.Intersect(Range("H9:H9"), Target) Is Nothing Then

'Borra el contenido de las celdas Apellidos

Cells(9, "I").ClearContents

Cells(9, "J").ClearContents

'Quita el color de celda de las columnas nombre y apellidos

Range("B2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("B2:D92").Interior.Pattern = xlNone

Range("B2:D92").Interior.TintAndShade = 0

Range("B2:D92").Interior.PatternTintAndShade = 0

Después de esto, guardamos el nombre en una variable y recorremos la columna “Nombre” del listado Excel para buscar un valor que coincida. En caso de encontrar alguno, marcamos el fondo de la celda en rojo.

'Guardamos en la variable nombre el valor introducido en la celda

nombre = Cells(9, "H")

'Marca en rojo los nombres del listado coincidentes con el contenido de la variable nombre

For i = 2 To 92

If Cells(i, "B") = nombre Then

Cells(i, "B").Interior.ColorIndex = 3

End If

Next

End If

Si cambia el contenido de la celda “1º Apellido” de la búsqueda, borramos el contenido de la celda “2º Apellido” de la búsqueda y establecemos el blanco de fondo en las celdas de las columnas “1º Apellido” y “2º Apellido” por si estuvieran marcadas en rojo de búsquedas anteriores.

'Si el valor del primer apellido cambia

If Not Application.Intersect(Range("I9:I9"), Target) Is Nothing Then

'Borra el contenido de la celda del Segundo Apellido

Cells(9, "J").ClearContents

'Quita el color de celda de las columnas apellidos

Range("C2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("C2:D92").Interior.Pattern = xlNone

Range("C2:D92").Interior.TintAndShade = 0

Range("C2:D92").Interior.PatternTintAndShade = 0

Guardamos el primer apellido en una variable y recorremos las columnas “Nombre” y “1º Apellido” del listado buscando campos coincidentes. En caso de encontrar alguno, marcamos el fondo de las celdas en rojo.

'Guardamos en la variable primerapellido el valor introducido en la celda

primerapellido = Cells(9, "I")

'Marca en rojo los nombres y primeros apellidos del listado coincidentes con el contenido de las variables nombre y primerapellido

For i = 2 To 92

If Cells(i, "B") = nombre And Cells(i, "C") = primerapellido Then

Cells(i, "C").Interior.ColorIndex = 3

End If

Next

End If

Si cambia el contenido de la celda “2º Apellido” de la búsqueda, establecemos el blanco de fondo en las celdas de la columna “2º Apellido” por si estuvieran marcadas en rojo de búsquedas anteriores.

'Si el valor del segundo apellido cambia

If Not Application.Intersect(Range("J9:J9"), Target) Is Nothing Then

'Quita el color de celda de la columna del segundo apellido

Range("D2:D92").Interior.ColorIndex = 2

'Restablecemos la cuadricula

Range("D2:D92").Interior.Pattern = xlNone

Range("D2:D92").Interior.TintAndShade = 0

Range("D2:D92").Interior.PatternTintAndShade = 0

Guardamos el segundo apellido en una variable y recorremos las columnas “Nombre”, “1º Apellido” y “2º Apellido” del listado buscando campos coincidentes. En caso de encontrar alguno, marcamos el fondo de las celdas en rojo.

'Guardamos en la variable segundoapellido el valor introducido en la celda

segundoapellido = Cells(9, "J")

'Marca en rojo los nombres y apellidos del listado coincidentes con el contenido de las variables nombre, 'primerapellido y segundoapellido

For i = 2 To 92

If Cells(i, "B") = nombre And Cells(i, "C") = primerapellido And Cells(i, "D") = segundoapellido Then

Cells(i, "D").Interior.ColorIndex = 3

End If

Next

End If

End Sub

Con esto llegamos al final de la vigesimoquinta lección, en la que has aprendido a buscar un valor en una hoja Excel usando VBA.

Por último, desear que te esté gustando el curso y pedirte que me plantees cualquier duda que te surja. También puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario. De hecho, soy todo oídos. Nos vemos en el próximo capítulo.

Descarga el archivo del ejemplo

« EP1. Cómo importar datos de otra hoja con macros.

EP3. Cómo clasificar automáticamente con macros. »

0 comentarios

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Quizá te interese,

Contacto