Este tutorial demostrará cómo usar los métodos Buscar y Reemplazar en Excel VBA.
Búsqueda de VBA
Excel tiene una excelente función incorporada Encontrar y Buscar y reemplazar instrumentos.
Se pueden activar con los atajos CTRL + F (Buscar) o CTRL + H (Reemplazar) oa través de la cinta: Inicio> Edición> Buscar y seleccionar.
Haciendo click Opciones, puede ver las opciones de búsqueda avanzada:
Puede acceder fácilmente a los métodos Buscar y Reemplazar usando VBA. Estos métodos integrados son mucho más rápidos que cualquier cosa que pueda escribir usted mismo en VBA.
Encuentre un ejemplo de VBA
Para demostrar la funcionalidad de Buscar, creamos el siguiente conjunto de datos en Sheet1.
Si desea seguir adelante, ingrese los datos en su propio libro de trabajo.
<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>
Búsqueda de VBA sin parámetros opcionales
Al usar el método Find de VBA, hay muchos parámetros opcionales que puede configurar.
Recomendamos encarecidamente definir 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 seleccionados actualmente en la ventana Buscar de Excel. Esto significa que es posible que no sepa qué parámetros de búsqueda se utilizan cuando se ejecuta el código. Find podría ejecutarse en todo el libro de trabajo o en una hoja. Podría buscar fórmulas o valores. No hay forma de saberlo, a menos que verifique manualmente lo que está seleccionado actualmente en la ventana Buscar de Excel.
Para simplificar, comenzaremos con un ejemplo sin parámetros opcionales definidos.
Ejemplo de búsqueda simple
Veamos un ejemplo sencillo de Find:
| 123456789 | Sub TestFind ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("empleado")MsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowEnd Sub |
Este código busca "empleado" en el rango usado de Sheet1. Si encuentra "empleado", asignará el primer rango encontrado a la variable de rango MyRange.
A continuación, se mostrarán cuadros de mensaje con la dirección, la columna y la fila del texto encontrado.
En este ejemplo, se utilizan las configuraciones de búsqueda predeterminadas (suponiendo que no se hayan 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 solo busca en una sola hoja de trabajo
Estos ajustes se pueden cambiar con varios parámetros opcionales (que se describen a continuación).
Encontrar notas sobre el método
- Buscar no selecciona la celda donde se encuentra el texto. Solo identifica el rango encontrado, que puede manipular en su código.
- El método Find solo ubicará la primera instancia encontrada.
- Puede utilizar comodines (*), p. Ej. buscar "E *"
Nada Encontrado
Si el texto de búsqueda no existe, el objeto de rango permanecerá vacío. Esto causa un problema importante cuando su código intenta mostrar los valores de ubicación porque no existen. Esto dará como resultado un mensaje de error que no desea.
Afortunadamente, puede probar un objeto de rango vacío dentro de VBA usando el operador Is:
| 1 | Si MyRange no es nada, entonces |
Añadiendo el código a nuestro ejemplo anterior:
| 12345678910111213 | Sub TestFind ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("empleado")Si MyRange no es nada, entoncesMsgBox MyRange.AddressMsgBox MyRange.ColumnMsgBox MyRange.RowDemásMsgBox "No encontrado"Terminara siEnd Sub |
Encontrar parámetros
Hasta ahora, solo hemos visto un ejemplo básico del uso del método Find. Sin embargo, hay varios parámetros opcionales disponibles para ayudarlo a refinar su búsqueda.
| Parámetro | Escribe | Descripción | Valores |
| Qué | Requerido | El valor a buscar | Cualquier tipo de datos, como una cadena o numérico |
| Después | Opcional | Referencia de celda única para comenzar su búsqueda | Dirección de la celda |
| Pase a ver | Opcional | Utilice fórmulas, valores y comentarios para la búsqueda | xlValues, xlFormulas, xlComments |
| Mirar | Opcional | Coincidir con parte o la totalidad de una celda | xlWhole, xlPart |
| BuscarOrden | Opcional | El orden en el que buscar: filas o columnas | xlByRows, xlByColummns |
| SearchDirection | Opcional | Dirección de la búsqueda para ingresar: hacia adelante o hacia atrás | xlNext, xlPrevious |
| MatchCase | Opcional | La búsqueda distingue entre mayúsculas y minúsculas o no | Verdadero o falso |
| MatchByte | Opcional | Se usa solo si ha instalado soporte de idioma de doble byte, p. Ej. idioma chino | Verdadero o falso |
| Formato de búsqueda | Opcional | Permitir buscar por formato de celda | Verdadero o falso |
Después de parámetro y encontrar varios valores
Usas el Después del parámetro para especificar la celda de inicio de su 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 sabe que se encontrarán más valores, utilice el método Buscar con el parámetro "Después" para registrar la primera instancia y luego use 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:
| 123456789101112131415161718192021222324252627282930313233343536 | Sub TestMultipleFinds ()Atenuar MyRange como rango, OldRange como rango, FindStr como cadena'Busque la primera instancia de "‘ Luz y calor "Establecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("Luz y calor")'Si no lo encuentra, salgaSi MyRange no es nada, salga de Sub'Mostrar la primera dirección encontradaMsgBox MyRange.Address'Hacer una copia del objeto de rangoEstablecer OldRange = MyRange'Agregue la dirección a la cadena delimitando con un "|" personajeFindStr = FindStr & "|" & MyRange.Address'Itere a través del rango buscando otras instanciasHacer"Buscar" Luz y calor "utilizando la dirección encontrada anteriormente como el parámetro DespuésEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("Light & Heat", After: = Range (OldRange.Address))'Si la dirección ya se ha encontrado, salga del bucle do; esto detiene el bucle continuoSi InStr (FindStr, MyRange.Address) Entonces Salga Hacer'Mostrar la última dirección encontradaMsgBox MyRange.Address'Agregue la última dirección a la cadena de direccionesFindStr = FindStr & "|" & MyRange.Address'hacer una copia del rango actualEstablecer OldRange = MyRangeCírculoEnd 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 calor".
Tenga en cuenta que el código seguirá repitiéndose hasta que se encuentre una dirección duplicada en FindStr, en cuyo caso saldrá del bucle Do.
Parámetro LookIn
Puedes usar el Parámetro LookIn para especificar en qué componente de la celda desea buscar. Puede especificar valores, fórmulas o comentarios en una celda.
- xlValues - Busca valores de celda (el valor final de una celda después de su cálculo)
- xlFormulas - Busca dentro de la fórmula de la celda (lo que se ingrese en la celda)
- xlComentarios - Búsquedas dentro de las notas de la celda
- xlCommentsThreaded - Búsquedas dentro de los comentarios de la celda
Suponiendo que se haya ingresado una fórmula en la hoja de trabajo, puede usar este código de ejemplo para encontrar la primera ubicación de cualquier fórmula:
| 12345678910 | Sub TestLookIn ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("=", LookIn: = xlFormulas)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siEnd Sub |
Si el parámetro "LookIn" se estableció en xlValues, el código mostraría un mensaje "No encontrado". En este ejemplo, devolverá B10.
Usar el parámetro LookAt
los Parámetro LookAt determina si buscar buscará una coincidencia de celda exacta o buscará cualquier celda que contenga el valor de búsqueda.
- xlTodo - 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 ubicará la primera celda que contiene el texto "light". Con Mira: = xlPart, devolverá una coincidencia para "Light & Heat".
| 123456789 | Sub TestLookAt ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("light", Lookat: = xlPart)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siEnd Sub |
Si xlTodo se estableció, una coincidencia solo se devolvería si el valor de la celda era "ligero".
Parámetro SearchOrder
los Parámetro SearchOrder dicta cómo se llevará a cabo la búsqueda en todo el rango.
- xlRows - La búsqueda se realiza fila por fila
- xlXolumnas - La búsqueda se realiza columna por columna
| 123456789 | Sub TestSearchOrder ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("empleado", SearchOrder: = xlColumns)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siEnd Sub |
Esto influye en qué coincidencia se encontrará primero.
Usando los datos de prueba ingresados en la hoja de trabajo anteriormente, cuando el orden de búsqueda son columnas, la celda ubicada es A5. Cuando el parámetro de orden de búsqueda se cambia a xlRows, la celda ubicada es C4
Esto es importante si tiene valores duplicados dentro del rango de búsqueda y desea encontrar la primera instancia bajo un nombre de columna en particular.
Parámetro SearchDirection
los Parámetro SearchDirection dicta en qué dirección se dirigirá la búsqueda, efectivamente hacia adelante o hacia atrás.
- xlSiguiente - Buscar el siguiente valor coincidente en el rango
- xlAnterior - Buscar valor coincidente anterior en el rango
Nuevamente, si hay valores duplicados dentro del rango de búsqueda, puede tener un efecto sobre cuál se encuentra primero.
| 12345678910 | Sub TestSearchDirection ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("calor", SearchDirection: = xlPrevious)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siEnd Sub |
Usando este código en los datos de prueba, una dirección de búsqueda de xlPrevious devolverá una ubicación de C9. El uso del parámetro xlNext devolverá una ubicación de A4.
El parámetro Siguiente significa que la búsqueda comenzará en la esquina superior izquierda del rango de búsqueda y trabajará hacia abajo. El parámetro Anterior significa que la búsqueda comenzará en la esquina inferior derecha del rango de búsqueda y continuará hacia arriba.
Parámetro MatchByte
los Parámetro MatchBye solo se utiliza para idiomas que utilizan un byte doble para representar cada carácter, como chino, ruso y japonés.
Si este parámetro se establece en "Verdadero", Find solo coincidirá con caracteres de doble byte con caracteres de doble byte. Si el parámetro se establece en "Falso", un carácter de doble byte coincidirá con caracteres de uno o dos bytes.
Parámetro SearchFormat
los Parámetro SearchFormat le permite buscar formatos de celda coincidentes. Puede ser una fuente en particular que se esté utilizando, una fuente en negrita o un color de texto. Antes de utilizar este parámetro, debe establecer el formato necesario para la búsqueda mediante la propiedad Application.FindFormat.
A continuación, se muestra un ejemplo de cómo utilizarlo:
| 12345678910111213 | Sub TestSearchFormat ()Atenuar MyRange como rangoApplication.FindFormat.ClearApplication.FindFormat.Font.Bold = TrueEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", Searchformat: = True)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siApplication.FindFormat.ClearEnd Sub |
En este ejemplo, el FindFormat La propiedad está configurada para buscar una fuente en negrita. Luego, la instrucción Find busca la palabra "calor" configurando el parámetro SearchFormat en True para que solo devuelva una instancia de ese texto si la fuente está en negrita.
En los datos de la hoja de trabajo de muestra mostrados anteriormente, esto devolverá A9, que es la única celda que contiene la palabra "calor" en negrita.
Asegúrese de que la propiedad FindFormat esté desactivada al final del código. Si no lo hace, su próxima búsqueda seguirá teniendo esto en cuenta y devolverá resultados incorrectos.
Cuando usa un parámetro SearchFormat, también puede usar un comodín (*) como valor de búsqueda. En este caso, buscará cualquier valor en negrita:
| 1 | Establecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("*", Searchformat: = True) |
Usando múltiples parámetros
Todos los parámetros de búsqueda discutidos aquí se pueden usar en combinación entre sí si es necesario.
Por ejemplo, puede combinar el parámetro "LookIn" con el parámetro "MatchCase" para ver todo el texto de la celda, pero distingue entre mayúsculas y minúsculas.
| 123456789 | Sub TestMultipleParameters ()Atenuar MyRange como rangoEstablecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("Luz y calor", LookAt: = xlWhole, MatchCase: = True)Si MyRange no es nada, entoncesMsgBox MyRange.AddressDemásMsgBox "No encontrado"Terminara siEnd Sub |
En este ejemplo, el código devolverá A4, pero si solo usamos una parte del texto, p. Ej. "Calor", no se encontraría nada porque coincidimos en la totalidad del valor de la celda. Además, fallaría debido a que el caso no coincide.
| 1 | Establecer MyRange = Sheets ("Sheet1"). UsedRange.Find ("heat", LookAt: = xlWhole, MatchCase: = True) |
Reemplazar en Excel VBA
Como es de esperar, existe una función Reemplazar en Excel VBA, que funciona de manera muy similar a "Buscar", pero reemplaza los valores en la ubicación de la celda encontrada con un nuevo valor.
Estos son los parámetros que puede utilizar en una instrucción del método Reemplazar. Estos funcionan exactamente de la misma manera que para la instrucción del método Find. La única diferencia con "Buscar" es que debe especificar un parámetro de reemplazo.
| Nombre | Escribe | Descripción | Valores |
| Qué | Requerido | El valor a buscar | Cualquier tipo de datos, como una cadena o numérico |
| Reemplazo | Requerido | La cadena de reemplazo. | Cualquier tipo de datos, como una cadena o numérico |
| Mirar | Opcional | Coincidir con parte o la totalidad de una celda | xlPart o xlWhole |
| BuscarOrden | Opcional | El orden de búsqueda en: filas o columnas | xlByRows o xlByColumns |
| MatchCase | Opcional | La búsqueda distingue entre mayúsculas y minúsculas o no | Verdadero o falso |
| MatchByte | Opcional | Se usa solo si ha instalado soporte de idioma de doble byte | Verdadero o falso |
| Formato de búsqueda | Opcional | Permitir buscar por formato de celda | Verdadero o falso |
| Reemplazar formato | Opcional | El formato de reemplazo del método. | Verdadero o falso |
El parámetro Reemplazar formato busca una celda con un formato particular, p. Ej. negrita de la misma manera que el parámetro SearchFormat opera en el método Find. Primero debe establecer la propiedad Application.FindFormat, como se muestra en el código de ejemplo de Buscar que se muestra anteriormente.
Reemplazar sin parámetros opcionales
En su forma más simple, solo necesita especificar qué está buscando y con qué desea reemplazarlo.
| 123 | Sub TestReplace ()Hojas ("Hoja1"). Rango usado.Remplazar qué: = "Luz y calor", Reemplazo: = "L & H"End Sub |
Tenga en cuenta que el método Find solo devolverá la primera instancia del valor coincidente, mientras que el método Reemplazar funciona en todo el rango especificado y reemplaza todo lo que encuentra una coincidencia.
El código de reemplazo que se muestra aquí reemplazará cada instancia de "Light & Heat" con "L & H" en todo el rango de celdas definido por el objeto UsedRange
Uso de VBA para buscar o reemplazar texto dentro de una cadena de texto VBA
Los ejemplos anteriores funcionan muy bien cuando se usa VBA para interactuar con datos de Excel. Sin embargo, para interactuar con cadenas de VBA, puede utilizar funciones de VBA integradas como INSTR y REPLACE.
Puedes usar el Función INSTR para ubicar una cadena de texto dentro de una cadena más larga.
| 123 | Sub TestInstr ()MsgBox InStr ("Esta es la cadena MyText", "MyText")End Sub |
Este código de ejemplo devolverá el valor de 9, que es la posición del número donde se encuentra "MiTexto" en la cadena que se va a buscar.
Tenga en cuenta que distingue entre mayúsculas y minúsculas. Si "MiTexto" está todo en minúsculas, se devolverá un valor de 0, lo que significa que no se encontró la cadena de búsqueda. A continuación, analizaremos cómo deshabilitar 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:
| 1 | MsgBox InStr (9, "Esta es la cadena MyText", "MyText") |
El punto de inicio se especifica como 9, por lo que aún devolverá 9. Si el punto de inicio fuera 10, devolvería 0 (sin coincidencia) ya que el punto de inicio estaría demasiado adelantado.
INSTR - Sensibilidad a mayúsculas y minúsculas
También puede establecer un parámetro de comparación para vbBinaryCompare o vbTextCompare. Si establece este parámetro, la declaración debe tener un valor de parámetro de inicio.
- vbBinaryCompare - Sensible a mayúsculas y minúsculas (predeterminado)
- vbTextCompare - No entre mayúsculas y minúsculas
| 1 | MsgBox InStr (1, "Esta es la cadena MyText", "mytext", vbTextCompare) |
Esta declaración seguirá devolviendo 9, aunque el texto de búsqueda esté en minúsculas.
Para deshabilitar la distinción entre mayúsculas y minúsculas, también puede declarar Option Compare Text en la parte superior de su módulo de código.
Función de reemplazo de VBA
Si desea reemplazar caracteres en una cadena con texto diferente dentro de su código, entonces el método Reemplazar es ideal para esto:
| 123 | Sub TestReplace ()MsgBox Replace ("Esta es la cadena MyText", "MyText", "My Text")End Sub |
Este código reemplaza "MyText" por "My Text". 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 agregar otros parámetros opcionales:
- Comienzo - 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 carácter definido por el parámetro Start.
- Contar - define el número de reemplazos a realizar. De forma predeterminada, Reemplazar cambiará cada instancia del texto de búsqueda que se encuentre, pero puede limitar esto a un solo reemplazo configurando el parámetro Recuento en 1
- Comparar - como en el método Find, puede especificar una búsqueda binaria o una búsqueda de texto usando vbBinaryCompare o vbTextCompare. El binario distingue entre mayúsculas y minúsculas y el texto no distingue entre mayúsculas y minúsculas
| 1 | MsgBox Replace ("Esta es la cadena MyText (mytext)", "MyText", "My Text", 9, 1, vbTextCompare) |
Este código devuelve "Mi cadena de texto (mytext)". Esto se debe a que el punto de inicio dado es 9, por lo que la nueva cadena devuelta comienza en el carácter 9. Solo se ha cambiado el primer "MiTexto" porque el parámetro Recuento está establecido en 1.
El método Reemplazar es ideal para resolver problemas como nombres de personas que contienen apóstrofos, p. Ej. O'Flynn. Si está utilizando comillas simples para definir un valor de cadena y hay un apóstrofo, esto provocará un error porque el código interpretará el apóstrofo como el final de la cadena y no reconocerá el resto de la cadena.
Puede usar el método Reemplazar para reemplazar el apóstrofo con nada, eliminándolo por completo.
