Usando Find and Replace (Buscar y Reemplazar) en Excel VBA - Automate Excel (2023)

Este tutorial demostrará cómo utilizar los métodos Buscar (Find) y Reemplazar (Replace) en Excel VBA.

Buscar en VBA

Excel tiene excelentes herramientas incorporadas de .

Se pueden activar con los atajos CTRL + B(Buscar) o CTRL + L(Reemplazar) o a través de la cinta de opciones: Inicio > Edición > Buscar y seleccionar.

Haciendo clic en Opciones, puede ver las opciones de búsqueda avanzada:

Puede acceder fácilmente a estos métodos utilizando VBA.

Ejemplo de Búsqueda VBA

Para demostrar la funcionalidad de Buscar, creamos el siguiente conjunto de datos en la Hoja1.

Si quiere seguir el ejemplo, introduzca los datos en su propio libro de trabajo.

Búsqueda VBA Sin Parámetros Opcionales

Cuando se utiliza el método VBA Find, hay muchos parámetros opcionales que puede establecer.

Le recomendamos encarecidamente que defina todos los parámetros siempre que utilice el método de búsqueda!

Si no define los parámetros opcionales, VBA utilizará los parámetros actualmente seleccionados en la ventana Buscar de Excel. Esto significa que no puede saber qué parámetros de búsqueda se están utilizando cuando se ejecuta el código. Buscar puede ser ejecutado en todo el libro o en una hoja. Podría buscar fórmulas o valores. No hay manera de saberlo, a menos que compruebe manualmente lo que está seleccionado en la ventana de búsqueda de Excel.

Para simplificar, comenzaremos con un ejemplo sin parámetros opcionales definidos.

Ejemplo de Búsqueda Simple

Veamos un ejemplo de búsqueda simple:

Sub PruebaBuscar() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Empleados") MsgBox MyRange.Address MsgBox MyRange.Column MsgBox MyRange.RowEnd Sub

Este código busca «Empleados» en el Rango Usado de la Hoja1. Si encuentra «Empleados», asignará el primer rango encontrado a la variable de rango MyRange.

A continuación, se mostrarán cuadros de mensaje con la dirección, columna y fila del texto encontrado.

En este ejemplo, se utiliza la configuración de búsqueda por defecto (asumiendo que no se ha cambiado en la ventana de búsqueda de Excel):

• El texto de búsqueda coincide parcialmente con el valor de la celda (no se requiere una coincidencia exacta de la celda)
• La búsqueda no distingue entre mayúsculas y minúsculas.
• Buscar sólo busca en una única hoja de cálculo

Estos ajustes pueden ser cambiados con varios parámetros opcionales (discutidos más adelante).

Notas Sobre el Método de Búsqueda

• Buscar no selecciona la celda donde se encuentra el texto. Sólo identifica el rango encontrado, que puede manipular en su código.
• El método Buscar sólo localizará la primera instancia encontrada.
• Puede utilizar comodines (*), por ejemplo, buscar ‘E*’

No Se Ha Encontrado Nada

Si el texto de búsqueda no existe, entonces el objeto rango permanecerá vacío. Esto causa un gran problema cuando su código intenta mostrar los valores de localización porque no existen. Esto dará lugar a un mensaje de error que usted no desea.

Afortunadamente, puede comprobar si un objeto de rango está vacío dentro de VBA utilizando el operador Is:

If Not MyRange Is Nothing Then

Añadiendo el código a nuestro ejemplo anterior:

Sub PruebaBuscar() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Empleados") If Not MyRange Is Nothing Then MsgBox MyRange.Address MsgBox MyRange.Column MsgBox MyRange.Row Else MsgBox "No Encontrado!" End IfEnd Sub

Parámetros de Búsqueda

Hasta ahora, sólo hemos visto un ejemplo básico de uso del método Find. Sin embargo, hay una serie de parámetros opcionales disponibles para ayudarle a refinar su búsqueda

Parámetro After (Después) y Búsqueda de Múltiples Valores

El parámetro After (Después) se utiliza para especificar la celda de inicio de la búsqueda. Esto es útil cuando hay más de una instancia del valor que está buscando.

Si una búsqueda ya ha encontrado un valor y se sabe que habrá más valores encontrados, entonces se utiliza el método Find con el parámetro ‘After’ para registrar la primera instancia y luego utilizar esa celda como punto de partida para la siguiente búsqueda.

Puede usar esto para encontrar múltiples instancias de su texto de búsqueda:

Sub PruebaBusquedaMultiplesInstancias() Dim MyRange As Range, OldRange As Range, FindStr As String 'Busque la primera instancia de "Luz y Calefacción" Set MyRange = Sheets("Hoja1").UsedRange.Find("Luz y Calefacción") 'Si no se encuentra, salir If MyRange Is Nothing Then Exit Sub 'Mostrar la primera dirección encontrada MsgBox MyRange.Address 'Hacer una copia del objeto de rango Set OldRange = MyRange 'Añade la dirección a la cadena delimitándola con un carácter "|". FindStr = FindStr & "|" & MyRange.Address 'Iterar a través del rango buscando otras instancias Do 'Busque "Luz y Calefacción" utilizando la dirección encontrada anteriormente como parámetro After Set MyRange = Sheets("Hoja1").UsedRange.Find("Luz y Calefacción", After:=Range(OldRange.Address)) 'Si la dirección ya ha sido encontrada, se sale del bucle do - esto detiene el bucle continuo If InStr(FindStr, MyRange.Address) Then Exit Do 'Mostrar la última dirección encontrada MsgBox MyRange.Address 'Añadir la última dirección a la cadena de direcciones FindStr = FindStr & "|" & MyRange.Address 'hacer una copia del rango actual Set OldRange = MyRange LoopEnd Sub

Este código iterará a través del rango utilizado, y mostrará la dirección cada vez que encuentre una instancia de ‘Luz y Calefacción’

Tenga en cuenta que el código seguirá en bucle hasta que se encuentre una dirección duplicada en FindStr, en cuyo caso saldrá del bucle Do.

Parámetro LookIn

Puede utilizar el parámetro LookIn para especificar en qué componente de la celda quiere buscar. Puede especificar valores, fórmulas o comentarios en una celda.

• xlValues – Busca los valores de las celdas (el valor final de una celda después de su cálculo)
• xlFormulas – Busca dentro de la propia fórmula de la celda (lo que se introduce en la celda)
• xlComments – Busca dentro de las notas de las celdas
• xlCommentsThreaded – Busca dentro de los comentarios de las celdas

Suponiendo que se ha introducido una fórmula en la hoja de trabajo, podría utilizar este código de ejemplo para encontrar la primera ubicación de cualquier fórmula:

Sub PruebaLookIn() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("=", LookIn:=xlFormulas) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End IfEnd Sub

Si el parámetro ‘LookIn’ estuviera establecido en xlValues, el código mostraría un mensaje de ‘No encontrado’. En este ejemplo devolverá B10.

Uso del Parámetro LookAt

El parámetro LookAt determina si find buscará una coincidencia de celda exacta, o buscará cualquier celda que contenga el valor buscado.

• xlWhole – Requiere que toda la celda coincida con el valor de búsqueda
• xlPart – Busca dentro de una celda la cadena de búsqueda

Este ejemplo de código localizará la primera celda que contenga el texto «Luz». Con LookAt:=xlPart, devolverá una coincidencia para «Luz y Calefacción».

Sub PruebaLookAt() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Luz", LookAt:=xlPart) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End IfEnd Sub

Si se establece xlWhole, sólo se obtendrá una coincidencia si el valor de la celda es «Luz».

Parámetro SearchOrder

El parámetro SearchOrder dicta cómo se realizará la búsqueda en todo el rango.

• xlRows – La búsqueda se realiza fila por fila
• xlColumns – La búsqueda se realiza columna por columna

Sub PruebaSearchOrder() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Empleados", SearchOrder:=xlColumns) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End IfEnd Sub

Esto influye en qué coincidencia se encontrará primero.

Utilizando los datos de prueba introducidos anteriormente en la hoja de trabajo, cuando el orden de búsqueda es columnas, la celda localizada es A5. Cuando el parámetro de orden de búsqueda se cambia a xlRows, la celda localizada es C4

Esto es importante si tienes valores duplicados dentro del rango de búsqueda y quieres encontrar la primera instancia bajo un nombre de columna particular.

Parámetro SearchDirection

El parámetro SearchDirection dicta en qué dirección irá la búsqueda – efectivamente hacia adelante o hacia atrás.

• xlNext – Buscar el siguiente valor coincidente en el rango
• xlPrevious – Buscar el valor anterior en el rango

De nuevo, si hay valores duplicados dentro del rango de búsqueda, puede tener un efecto sobre cuál se encuentra primero.

Sub PruebaSearchDirection() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Calefacción", SearchDirection:=xlPrevious) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End IfEnd Sub

Usando este código en los datos de prueba, una dirección de búsqueda de xlPrevious devolverá una ubicación de C9. Si se utiliza el parámetro xlNext se obtendrá la posición A4.

El parámetro Next significa que la búsqueda comenzará en la esquina superior izquierda del rango de búsqueda y trabajará hacia abajo. El parámetro Previous (Anterior) significa que la búsqueda comenzará en la esquina inferior derecha del rango de búsqueda y trabajará hacia arriba.

Parámetro MatchByte

El parámetro MatchBye sólo se utiliza para los idiomas que utilizan un byte doble para representar cada carácter, como el chino, el ruso y el japonés. Si este parámetro se establece como ‘True’ entonces Buscar sólo coincidirá con caracteres de doble byte con caracteres de doble byte. Si el parámetro se establece en ‘False’, entonces un carácter de doble byte coincidirá con caracteres de uno o dos bytes.

Parámetro SearchFormat

El parámetro SearchFormat le permite buscar formatos de celda que coincidan. Puede tratarse de un tipo de letra concreto que se esté utilizando, o una fuente en negrita, o un color de texto. Antes de utilizar este parámetro, debe establecer el formato requerido para la búsqueda utilizando la propiedad Application.FindFormat.

A continuación se muestra un ejemplo de cómo utilizarlo:

Sub PruebaSearchFormat() Dim MyRange As Range Application.FindFormat.Clear Application.FindFormat.Font.Bold = True Set MyRange = Sheets("Hoja1").UsedRange.Find("Calefacción", Searchformat:=True) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End If Application.FindFormat.ClearEnd Sub

En este ejemplo, la propiedad FindFormat se establece para buscar una fuente en negrita. La sentencia Find busca entonces la palabra ‘Calefacción’ ajustando el parámetro SearchFormat a True para que sólo devuelva una instancia de ese texto si la fuente es negrita.

En los datos de la hoja de trabajo de ejemplo mostrados anteriormente, esto devolverá A9, que es la única celda que contiene la palabra ‘Calefacción’ en negrita.

Asegúrese de que la propiedad FindFormat se borra al final del código. Si no lo hace, la siguiente búsqueda seguirá teniendo en cuenta esta propiedad y devolverá resultados incorrectos.

Si utiliza el parámetro SearchFormat, también puede utilizar un comodín (*) como valor de búsqueda. En este caso, se buscará cualquier valor con una fuente en negrita:

Set MyRange = Sheets("Hoja1").UsedRange.Find("*", Searchformat:=True)

Uso de múltiples Parámetros

Todos los parámetros de búsqueda aquí comentados pueden utilizarse en combinación con otros si es necesario.

Por ejemplo, se puede combinar el parámetro «LookIn» con el parámetro «MatchCase» para buscar en todo el texto de la celda, pero distinguiendo entre mayúsculas y minúsculas

Sub PruebaMultiplesParametros() Dim MyRange As Range Set MyRange = Sheets("Hoja1").UsedRange.Find("Luz y Calefacción", LookAt:=xlWhole, MatchCase:=True) If Not MyRange Is Nothing Then MsgBox MyRange.Address Else MsgBox "No Encontrado" End IfEnd Sub

En este ejemplo, el código devolverá A4, pero si sólo utilizáramos una parte del texto, por ejemplo «calefacción», no se encontraría nada porque estamos comparando todo el valor de la celda. Además, fallaría debido a la no coincidencia de mayúsculas y minúsculas.

Set MyRange = Sheets("Hoja1").UsedRange.Find("calefacción", LookAt:=xlWhole, MatchCase:=True)

Reemplazar en Excel VBA

Existe, como es de esperar, una función Reemplazar en Excel VBA, que funciona de forma muy similar a ‘Buscar’, pero sustituye los valores en la ubicación de la celda encontrada por un nuevo valor.

Estos son los parámetros que puedes utilizar en una sentencia del método Reemplazar. Funcionan exactamente igual que la sentencia del método Buscar. La única diferencia con el método «Buscar» es que es necesario especificar un parámetro de reemplazo.

El parámetro ReplaceFormat busca una celda con un formato particular, por ejemplo, negrita, de la misma manera que el parámetro SearchFormat opera en el método Find. Es necesario establecer primero la propiedad Application.FindFormat, como se muestra en el código de ejemplo de Find mostrado anteriormente

Reemplazar Sin Parámetros Opcionales

En su forma más simple, sólo necesita especificar lo que está buscando y con qué quiere reemplazarlo.

Sub PruebaReemplazar() Sheets("Hoja1").UsedRange.Replace What:="Luz y Calefacción", Replacement:="L & C"End Sub

Tenga en cuenta que el método Buscar sólo devolverá la primera instancia del valor coincidente, mientras que el método Reemplazar trabaja a través de todo el rango especificado y reemplaza todo lo que encuentra una coincidencia.

El código de reemplazo mostrado aquí reemplazará cada instancia de ‘Luz y Calefacción’ con ‘L & C’ a través de todo el rango de celdas definido por el objeto UsedRange

Usando VBA para Buscar o Reemplazar Texto Dentro de una Cadena de Texto VBA

Los ejemplos anteriores funcionan muy bien cuando se utiliza VBA para interactuar con los datos de Excel. Sin embargo, para interactuar con cadenas de texto VBA, puede utilizar funciones VBA incorporadas como INSTR y REPLACE.

Puede utilizar la función Función INSTR para localizar una cadena de texto dentro de una cadena más larga.

Sub PruebaInstr() MsgBox InStr("Esta es la cadena MiTexto", "MiTexto")End Sub

Este código de ejemplo devolverá el valor de 19, que es la posición numérica donde se encuentra ‘MiTexto’ en la cadena a buscar.

Tenga en cuenta que distingue entre mayúsculas y minúsculas. Si ‘MiTexto’ está todo en minúsculas, entonces se devolverá un valor de 0, lo que significa que no se encontró la cadena de búsqueda. A continuación veremos cómo desactivar la distinción entre mayúsculas y minúsculas.

INSTR – Inicio

Hay otros dos parámetros opcionales disponibles. Puede especificar el punto de inicio de la búsqueda:

MsgBox InStr(19, "Esta es la cadena MiTexto", "MiTexto")

El punto de inicio se especifica como 19, por lo que devolverá 19. Si el punto de inicio fuera 20, entonces devolvería 0 (ninguna coincidencia) ya que el punto de inicio estaría demasiado adelantado.

INSTR – Sensible a Mayúsculas y Minúsculas

También puede establecer el parámetro Compare como vbBinaryCompare o vbTextCompare. Si establece este parámetro, la sentencia debe tener un valor de parámetro de inicio.

• vbBinaryCompare – Sensible a mayúsculas y minúsculas (por defecto)
• vbTextCompare – No distingue mayúsculas y minúsculas

MsgBox InStr(1, "Esta es la cadena MiTexto", "mitexto", vbTextCompare)

Esta sentencia seguirá devolviendo 19, aunque el texto de búsqueda esté en minúsculas. Para desactivar la distinción entre mayúsculas y minúsculas, también puede declarar la Option Compare Texten la parte superior de su módulo de código.

Función VBA Replace

Si desea reemplazar los caracteres de una cadena con un texto diferente dentro de su código, entonces el método Replace es ideal para esto:

Sub PruebaReplace() MsgBox Replace("Esta es la cadena MiTexto", "MiTexto", "Mi Texto")End Sub

Este código reemplaza ‘MiTexto’ con ‘Mi Texto’. Tenga en cuenta que la cadena de búsqueda distingue entre mayúsculas y minúsculas, ya que la comparación binaria es la predeterminada.

También puede añadir otros parámetros opcionales:

• Start – define la posición en la cadena inicial desde la que debe comenzar el reemplazo. A diferencia del método Find, devuelve una cadena truncada a partir del número de caracteres definido por el parámetro Start.
• Count – define el número de reemplazos a realizar. Por defecto, Reemplazar cambiará cada instancia del texto de búsqueda encontrado, pero puede limitarlo a un solo reemplazo estableciendo el parámetro Count a 1
• Compare– al igual que en el método Find, puede especificar una búsqueda binaria o una búsqueda de texto utilizando vbBinaryCompare o vbTextCompare. El binario distingue entre mayúsculas y minúsculas y el texto no distingue entre mayúsculas y minúsculas

MsgBox Replace("Esta es la cadena MiTexto (mitexto)", "MiTexto", "Mi Texto", 19, 1, vbTextCompare)

Este código devuelve ‘Mi Texto (mitexto)’. Esto se debe a que el punto de inicio dado es 19, por lo que la nueva cadena devuelta comienza en el carácter 19. Sólo se ha modificado el primer ‘MiTexto’ porque el parámetro Count está establecido en 1.

El método Replace es ideal para resolver problemas como los nombres de personas que contienen apóstrofes, por ejemplo, O’Flynn. Si está usando comillas simples para definir un valor de cadena y hay un apóstrofe, esto causará un error porque el código interpretará el apóstrofe como el final de la cadena y no reconocerá el resto de la cadena.

Puede utilizar el método Replace para sustituir el apóstrofe por nada, eliminándolo por completo.