busqueda multiple en una y todas las hojas

En varias entradas del blog podrán leer distintas "técnicas de búsqueda", realizadas siempre con el objetivo de saber si ese dato que tanto nos preocupa existe o no dentro de una hoja o rango. Dichas técnicas pueden o no hacer uso de las macros; muchas veces con funciones como BUSCARV() o COINCIDIR(), y otras tantas, alcanza. Para la consulta que me realizan en esta oportunidad no veo mejor alternativa que hacer uso de VBA. dado que no es un búsqueda "común y corriente".

La pregunta es la siguiente: "como puedo hacer para buscar un valor dentro de todas las hojas del libro, almacenando la celda en donde están y luego permitir que el usuario vaya hasta esa dirección". La respuesta no es muy complicada, pero tiene sus cosas. Empecemos.

mi Lector seguro se inspiró en la herramienta "Buscar" de Excel al realizarme esta consulta.

[+/-] Ver el resto / Ocultar

Analicemos la imagen: le estoy pidiendo que me busque el valor "a" dentro de todo el libro (accedí a esta característica presionando el botón "Opciones") y, debajo, el formulario me devuelve todas las coincidencias, indicando la hoja y la celda en donde dicho valor se encuentra. 

El método que usaremos para resolver esta cuestión es Find ¿lo recuerdan? Ya lo hemos aplicado en varias oportunidades, especialmente para saber en que fila se halla nuestro valor. Una de las tantas formas podría ser esta:

Fila = Range("A1:A1000").Find("a").Row

Si "a" se encuentra presente en el rango A1:A1000, en Fila almacenamos el número de fila de la celda en la cual está "a". Si no es así, el método Find nos devuelve un error.

Pero esto no alcanza, lamentablemente. Si "a" está repetido en varias celdas y ejecutamos la macro, siempre encontrará el primero y se terminará la rutina. Es decir: nunca accedemos al segundo, tercer, n valor repetido. De aquí ya vamos infiriendo que nuestro procedimiento necesitará un bucle, para encontrar  todos los "a" que existan dentro del rango especificado.

Find posee varios argumentos, los cuales debemos tener muy presentes a la hora de buscar un valor; repasemos los mas importantes para este proyecto:

What: que buscamos, así de sencillo. Este argumento es obligatorio.

LookIn: opcional. Si buscaremos dentro de fórmulas (xlFormulas) o valores (xlValues)

LookAt: opcional. Si será coincidencia exacta (xlWhole) o bien que busque dentro del texto (xlPart)

(aclaración: con xlPart, por ejemplo, encontramos "excel" si una celda tiene: "blog de excel", o sea busca el valor dentro de una cadena)

Sigamos. Tengo estos datos en una planilla de cálculos, y quiero encontrar todas las celdas que posean la palabra "blog":

en A1 y D8 están los valores que busco.

 El código es poco y se encuentra comentado, para una mejor interpretación:

Sub BuscarTodo()
Dim Celda
Dim Pd As String

'busco en todas las celdas de la hoja activa:
With ActiveSheet.Cells
    'seteo a Celda, buscando la palabra "blog" dentro de los valores
    '(xlValues) y que la coincidencia sea exacta (xlWhole)
    Set Celda = .Find("blog", LookIn:=xlValues, LookAt:=xlWhole)
    'si "blog" existe (o sea que no devuelve "nothing")
    If Not Celda Is Nothing Then
        'tomo la dirección
        Pd = Celda.Address
        'y aquí es donde entra a jugar el bucle: ejecuto a FindNext
        Do
            Set Celda = .FindNext(Celda)
            'muestro en un mensaje la dirección:
            MsgBox Celda.Address
        'ejecuto el código mientras encuentre el valor o la
        'dirección sea distinta a la primera que encontró:
        Loop While Not Celda Is Nothing And Celda.Address <> Pd
    End If
End With

Set Celda = Nothing
End Sub

Aquí debemos tener presente algo muy importante:

Loop While Not Celda Is Nothing And Celda.Address <> Pd

En esa línea estoy dando las indicaciones exactas de hasta cuando se debe ejecutar el bucle: hasta que Celda sea igual a Nothing (es decir, nulo, sin resultados) o bien que la dirección de la celda sea distinta al primer valor hallado. Este último punto es fundamental, ya que de no existir nuestro bucle entraría en una acción "infinita", lo cual es algo totalmente indeseado.

Les muestro las dos imágenes correspondientes:

bien, la macro halló los dos "blog" presentes en la hoja, indicando en que celdas se encuentran.

Digamos que el tema se encontraría ya casi casi "cocinado". Restaría colocar ese código en un UserForm que contenga un textbox para que el usuario introduzca el valor a buscar, junto a otro control que nos permita listar los resultados obtenidos (hoja y celda en donde se halla el dato).
En la siguiente imagen vemos algo parecido:

el (pobre) aspecto estético de mi Form seguro será mejorado por Uds.

Al código casi no le hacemos cambios: en lugar de mostrar el Address de la celda que contiene el valor buscado, lo colocaremos como un Item del ListBox. Eso y que el dato a buscar lo tomaremos del textbox.

Private Sub cmdBuscar_Click()
Dim Celda, Valor
Dim Pd As String

Valor = txtValor.Text

'si detecto que el cuadro de texto está vacío, aviso
'y salgo del procedimiento:
If Trim(txtValor.Text) = "" Then
    MsgBox "No ha ingresado ningún dato", vbExclamation
    Exit Sub
End If
'limpio al listbox:
lstCeldas.Clear
'busco en todas las celdas de la hoja activa:
With ActiveSheet.Cells
    'seteo a Celda, buscando la palabra "blog" dentro de los valores
    '(xlValues) y que la coincidencia sea exacta (xlWhole)
    'paso como argumento el dato introducido en txtValor
    Set Celda = .Find(Valor, LookIn:=xlValues, LookAt:=xlWhole)
    'si "blog" existe (o sea que no devuelve "nothing")
    If Not Celda Is Nothing Then
        'tomo la dirección
        Pd = Celda.Address
        'y aquí es donde entra a jugar el bucle: ejecuto a FindNext
        Do
            Set Celda = .FindNext(Celda)
            'en el listbox agrego el nombre de la hoja y, separado
            'por un guión medio, el Address
            lstCeldas.AddItem ActiveSheet.Name & "-" & Celda.Address
        'ejecuto el código mientras encuentre el valor o la
        'dirección sea distinta a la primera que encontró:
        Loop While Not Celda Is Nothing And Celda.Address <> Pd
    End If
End With

Damos salida a nuestro userForm y ejecutamos la rutina, presionando el botón "Buscar":

bien bien bien, ya me estoy acercando a lo que me solicitó mi estimado lector.

Como podrán apreciar en la imagen superior, tengo en el listbox las dos coincidencias: la palabra "blog" se encuentra en la hoja1, celdas A1 y D8.
Recordarán que me solicitaron que al hacer click sobre los resultados (items del listbox) una macro me lleve hasta ese lugar. Entonces nos vamos a la ventana de código del control y dentro del evento Click escribimos estas líneas de código:

Private Sub lstCeldas_Click()
Dim V
Dim H, C As String
'el código solo se ejecutará si el listbox tiene algún
'item dentro de el:
If lstCeldas.ListCount > -1 Then
    V = Split(lstCeldas.Text, "-")
    H = V(0)
    C = V(1)
    Sheets(H).Select
    Range(C).Select
End If
End Sub

Ahora se darán cuenta que a propósito utilicé un guión medio para separar el nombre de la hoja y la dirección de la celda. ¿Por qué? Por que luego tomo ese guión medio y lo uso como separador (segundo argumento) de Split, convirtiendo esa cadena de texto en una matriz. Entonces: en la primer posición de la matriz [V(0)] almacené el nombre de la hoja y en la segunda [V(1)] la dirección (address) de la celda. Y en la última línea selecciono la hoja y la celda.
Si ahora  hago click sobre el listbox:

vean la imagen: clickeé sobre el segundo item y la macro seleccionó el rango D8 de la hoja1. Todo funciona correctamente.

Me olvidaba: mi lector me pidió que la búsqueda se realice en todo el libro. Ja, y casi cierro la entrada sin darle solución a este punto. En realidad es mucho mas fácil de lo que parece: a todo el código anterior debo "meterlo" dentro de un bucle que recorra todas las hojas del libro, repitiendo en cada una de ellas el código visto hasta el momento:

Private Sub cmdBuscar_Click()
Dim Celda, Valor
Dim Pd As String
Dim Hoja As Worksheet

Valor = txtValor.Text

'si detecto que el cuadro de texto está vacío, aviso
'y salgo del procedimiento:
If Trim(txtValor.Text) = "" Then
    MsgBox "No ha ingresado ningún dato", vbExclamation
    Exit Sub
End If
'limpio al listbox:
lstCeldas.Clear
'busco en todas las hojas del libro:
For Each Hoja In ActiveWorkbook.Sheets
    With Hoja.Cells
        'seteo a Celda, buscando la palabra "blog" dentro de los valores
        '(xlValues) y que la coincidencia sea exacta (xlWhole)
        'paso como argumento el dato introducido en txtValor
        Set Celda = .Find(Valor, LookIn:=xlValues, LookAt:=xlWhole)
        'si "blog" existe (o sea que no devuelve "nothing")
        If Not Celda Is Nothing Then
            'tomo la dirección
            Pd = Celda.Address
            'y aquí es donde entra a jugar el bucle: ejecuto a FindNext
            Do
                Set Celda = .FindNext(Celda)
                'en el listbox agrego el nombre de la hoja y, separado
                'por un guión medio, el Address
                lstCeldas.AddItem Hoja.Name & "-" & Celda.Address
            'ejecuto el código mientras encuentre el valor o la
            'dirección sea distinta a la primera que encontró:
            Loop While Not Celda Is Nothing And Celda.Address <> Pd
        End If
    End With
Next Hoja

Set Celda = Nothing
Set Hoja = Nothing
End Sub

Habrán visto que la modificación fue mínima, pero nos permite buscar un valor en todas las hojas del libro, registrando en el listbox donde se encuentra:

Terminamos. ¿Otras ideas? Podríamos incorporar a ese formulario si queremos buscar en ciertas hojas, dentro de cualquier cadena (xlPart), etc, etc, etc, hasta convertirlo en algo muy parecido a la herramienta que nos brinda Excel, pero totalmente personalizado y funcional a nuestro proyecto.
¿Una mas? Imaginemos que deseamos evitar que los usuarios ingresen cualquier dato duplicado dentro del libro. ¿Como hacemos para controlar que lo que puso en A1 de la Hoja1 no se encuentre en C10 de la Hoja8? Usando este código: si devuelve alguna coincidencia, le avisamos donde está y evitamos que pueda colocar información duplicada.
Las aplicaciones son muchas y los ejemplos pueden llegar a cientos. Solo es cuestión de adaptar un poco el código a nuestras necesidades.
Gracias por los mensajes que a diario recibo, cualquier duda la vemos.
Link al archivo.