buscar valores en todas las hojas del libro

En esta consulta, Mauricio me pregunta como buscar un valor en varias hojas de un libro. Si la cuestión fuese solamente esa, la respuesta sería sencilla: configurar de forma adecuada el cuadro de diálogo respectivo (edición / buscar):

al colocar "Dentro de:" Libro haremos que Excel nos liste todos los resultados obtenidos, como se observa al pié del formulario.

[+/-] Ver el resto / Ocultar

Pero mi lector debe: buscar un valor determinado hoja por hoja y si ese dato se encuentra, aplica un Filtro Avanzado. Y ahí es donde la herramienta arriba mostrada no nos alcanza, tenemos que recurrir a las macros.

Recordemos como es el código para efectuar una búsqueda:

Range("A1:A1000").Find(valor)

Si el argumento que pasamos a Find, "valor" en este caso, no se encuentra, VBA nos devolverá un mensaje de error: el nro 91.

Pero también existe otra forma de hacer lo mismo, utilizando las WorkSheetFunctions de Excel, llamando a la función BUSCARV(), que en inglés sería VLOOKUP():

Application.WorksheetFunction.VLookup("excel", Range("A1:A1000"), 1, 0)

Como verán, se utilizan los mismos argumentos (valor, rango de busqueda, columna a devolver, ordenado)

Encaró su labor de esta forma, así que daremos una solución acorde.

El valor que el desea buscar está en el rango A2 de la Hoja1. Nuestra macro debe:

1) Almacenar el valor

2) Buscarlo en todas las hojas

3) Si lo encuentra, ejecutar una determinada acción y seguir

4) Si no lo encuentra va hacia la próxima hoja, hasta finalizar con todas las del libro.

Por el ejemplo que me pasa, tiene problemas cuando se produce el error 1004, nro que arroja VBA con las Worksheetfunction al no encontar un valor. Utilizaré la instrucción On Error Resume Next que no es mas ni menos que un "controlador de errores": lo detecta pero no interrumpe el flujo del programa, permitiéndonos hacer una cosa u otra, dependiendo del error producido.

Un pequeño código para entenderlo sería:

On Error Resume Next

Resultado = 100 / 0

Ir Err.Number <> 0 then

  MsgBox "No se puede dividir por cero"

  Err.Clear

Else

  MsgBox "La división es correcta"

End If

Todos sabemos la que multiplicación por cero no existe, así que ahí obtengo un error. Pero antes utilicé al "capturador" e inmediatamente debajo de la línea que produce el problema con un If.. pregunto: si el número de error es distinto a 0 (cero), avisá que algo salió mal.... y limpiá el error, para que no persista (Clear).

Caso contrario, aplico el código respectivo.

Otro inconveniente a tener en cuenta: pueden existir dentro del libro muchas hojas en donde no deseemos efectuar la búsqueda. Dentro los cientos de opciones posibles, casi siempre elijo esta: antepongo al nombre de la hoja algún código o juego de caracteres que las diferencie del resto. Entonces luego (y dentro del bucle que utilizo para recorrer las Sheets) pregunto, antes de buscar, si el nombre de la hoja comienza de una forma en particular. Algo así:

For Each Hoja In ActiveWorkbook.Sheets

  If Left(Hoja.Name="TB", 2) then

     'aqui va el código a ejecutar

  End If

Next Hoja

Lo arriba expuesto es sencillo: recorro las hojas con For Each... cada vez que encuentra una nueva se pregunta: ¿el nombre empieza con TB? Si es así, escribo el código, que en el caso de mi lector sería colocar un Filtro Avanzado, pero podría ser cualquier cosa: borrar un rango, enviar la hoja hacia Access o un Txt, imprimirla, insertar una Tabla Dinámica etc, etc, etc, etc. Queda como en la siguiente imagen:

Con todo lo visto y explicado hasta el momento, estamos en condiciones de analizar el siguiente código, el cual se encuentra bien comentado para una mejor interpretación:

Sub BusquedaMultiple()
'variables de uso local
Dim Sht As Worksheet
Dim Rng As Range
Dim Crit
Dim Fila As Long

'tomo el valor a buscar:
Crit = Sheets("hoja1").Range("dato").Value
'con un bucle recorro todas las hojas
For Each Sht In ActiveWorkbook.Sheets
    'pero solamente busco en aquellas que comienzan con TB.
    'para esto uso a la funcion Left() y Lcase, para que TB
    'siempre lo compare en minúsculas
    If LCase(Left(Sht.Name, 2)) = "tb" Then
        'seteo el rango de búsqueda
        Set Rng = Sht.Range("a1:a50000")
        'y hago el BuscarV con sus 4 argumentos:
        'dato, rango, columna, ordenado
        On Error Resume Next
        'coloco a VLookup
        If Crit = Application.WorksheetFunction.VLookup(Crit, Rng, 1, 0) Then
            'si no se produce ningun error:
            If Err.Number = 0 Then
                'aplico el código del filtro o bien la acción que
                'deseo realizar sobre la hoja.
                '
               
                'aqui solo incluyo un MsgBox alertando sobre la
                'coincidencia encontrada:
                MsgBox "el valor " & Crit & " está en la hoja " & Sht.                Name
            ElseIf Err.Number = 1004 Then
                'si aparece el error 1004 (o sea que el dato no está)
                'simplemente lo limpio:
                Err.Clear
            Else
                'si se produce algún otro tipo de error, aviso x las
                'dudas
                MsgBox Err.Description, vbCritical, "Error: " & Err.Number
            End If
        End If
    End If
Next Sht

'destruyo los objetos, para liberar recursos
Set Sht = Nothing
Set Rng = Nothing
End Sub

Este código no es mas que un "rejunte" de todo lo que vinimos viendo hasta ahora: tomar el valor a buscar, recorrer una x una las hojas y si dicho dato está, se ejecutará una determinada acción. El bloque If dentro del bucle hace un buen análisis de distintos escenarios: si el número de error es igual a 0 (no hay error) todo sigue bien, si el error es 1004 (no encontró el valor buscado) limpia el error... y ese Else se asegura de avisar ante cualquier número de error no previsto por nosotros. Si la situación lo amerita, podríamos colocar un End para dar fin a la macro, en caso de inconvenientes no previstos.

Espero que les sea de utilidad, aquí les dejo el link al archivo.

Salu2.xlsx