Código VBA para buscar celdas en un rango - Range.Find

El método Find de la clase Range se utiliza para buscar celdas que contengan un valor o formato determinado en un rango.

Buscar en la hoja de cálculo

En su forma básica, Find se llama como el código siguiente, que selecciona la celda encontrada. El código sólo funcionará si el valor está realmente presente en la hoja de cálculo.


ActiveSheet.UsedRange.Find(What:="x").Select

Nota: Para mantener el código de ejemplo corto utilizamos el método ActiveSheet para proporcionar el objeto Hoja de cálculo padre cuya clase Range se está utilizando. En código real es preferible utilizar variables de objeto explícitamente declaradas y asignadas, en este caso: Dim wsActiveSheet As Worksheet: Set wsActiveSheet = ActiveSheet, muy probablemente con otra expresión de asignación de objeto.

El objeto de clase Range se obtiene aquí del procedimiento Worksheet UsedRange.

El procedimiento devuelve un objeto de tipo Range que se asigna opcionalmente a una variable rngFound. Generalmente, se utilizará una variable, aquí: strWhat para asignar el parámetro What.


Dim strWhat As String: strWhat =
Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:=strWhat)

El método Find devuelve Nothing si no se encuentra ninguna coincidencia.

Nota: El método Buscar no afecta a la selección ni a la celda activa.

Buscar en rango

Buscar valor en columna

El código siguiente le permite encontrar una celda que contenga el valor de una columna determinada.


Dim rngColumn As Range
Set rngColumn = ActiveSheet.Columns(3)
Dim rngFound As Range
Set rngFound = rngColumn.Find(What:="x").Select

Buscar valor en la fila

Utilice el siguiente código para encontrar una celda que contenga el valor en una fila.


Dim rngRow As Range
Set rngRow = ActiveSheet.Rows(8)
Dim rngFound As Range
Set rngFound = rngRow.Find(What:="x")

Qué: especificar criterios de búsqueda

Find dialog options — Los parámetros del método Buscar de Excel se corresponden en gran medida con las opciones del cuadro de diálogo Buscar.

Encuentre lo que

Los datos a buscar. Puede ser una cadena o cualquier tipo de dato de Microsoft Excel. Algunos consejos sobre los valores adecuados de los parámetros:

Para buscar valores numéricos, utilice Lookin: =xlFormulas y LookAt:=xlWhole
Cuando busque valores de fecha utilice Lookin:=xlFormulas y LookAt:=xlWhole. La fecha suministrada debe ser como 25/02/2024 que se puede obtener utilizando FormatDateTime(dtDate, vbGeneralDate)

Advertencia:

Al buscar valores numéricos, LookAt:=xlPart puede hacer que encuentre más números de los que desea: si busca 1 también obtendrá 301, 12 y así sucesivamente.
Si las columnas son más pequeñas, es posible que las celdas no muestren el valor, sino ###. Puede estar más seguro combinando conLookin:=xlFormulas

After: celda de inicio de la búsqueda

Si se ha especificado After, la búsqueda comienza después de esta celda; la celda especificada no se busca hasta que el método vuelve a esta celda.

Nota: Si no especifica este argumento, la búsqueda comienza después de la celda situada en la esquina superior izquierda del rango A1. Esto es diferente de usar el diálogo Buscar, que empieza en la CeldaActiva.

Error: Si se utiliza, el argumento After debe ser una única celda del rango. Si no es así, se obtiene el error 13: Type mismatch.

El código siguiente inicia la búsqueda después de la celda activa imitando el cuadro de diálogo Buscar


Dim rngFound As Range
Set rngFound = ws.UsedRange.Find(What:="x", After:=ActiveCell)

LookIn: Fórmulas, Valores, Notas, Comentarios

Puede ser una de las siguientes constantes XlFindLookIn: xlFormulas (Fórmulas), xlValues (Valores), xlComments (Notas) o xlCommentsThreaded (Comentarios).


Dim rngFound As Range 
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookIn:=xlComments)

xlFormulas vs xlValores

La diferencia entre Buscar fórmulas o valores puede observarse experimentalmente. Ponga en A1 el valor x y en B1 la fórmula =A1. En el diálogo Buscar pulse Buscar todo. Con Fórmulas seleccionadas sólo se encuentra A1 mientras que con Valores se encuentran tanto A1 como B1.

Conclusión:

Fórmulas mira lo que contiene la celda como lo que está disponible en la barra de fórmulas;
Los valores coinciden con lo que se muestra como valor en la celda de la cuadrícula.

Atención: Los valores de LookIn, LookAt y SearchOrder se guardan cada vez que se utiliza este método. Si no especifica valores para estos argumentos la próxima vez que llame al método, se utilizarán los valores guardados. La configuración de estos argumentos cambia la configuración del cuadro de diálogo Buscar, y la modificación de la configuración del cuadro de diálogo Buscar cambia los valores guardados que se utilizan si se omiten los argumentos.

Consejos: Para evitar problemas, establezca LookIn, LookAt y SearchOrder explícitamente cada vez que utilice el método Find.

Nota: Lamentablemente, los valores actuales utilizados en Buscar no pueden recuperarse (por ejemplo, desde la configuración de propiedades de la aplicación), por lo que no es posible restaurarlos después de su uso. Afortunadamente, los valores vuelven a sus valores predeterminados después de cerrar Excel.

LookAt: Coincidir con todo el contenido de la celda

Puede ser una de las siguientes constantes XlLookAt: xlWhole o xlPart. Utilice xlWhole si necesita encontrar una celda con un texto específico.

Añada MatchCase:=True si necesita encontrar una coincidencia exacta en el rango (distingue mayúsculas de minúsculas).


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", LookAt:=xlWhole, MatchCase:=True)

Uso de comodines

En Excel tiene un uso limitado de caracteres comodín en el argumento Buscar:

un signo de interrogación (?) coincide con un carácter si está dentro de una cadena;
poner un signo de interrogación al principio o al final también coincide si hay más de 1 carácter.
Un asterisco (*) coincide con cualquier serie de caracteres.

Como ejemplo, el siguiente código encuentra cualquier celda que coincida con 'x'.


Set rngFound = ActiveSheet.UsedRange.Find(What:="?x*")

El procedimiento TestFindAll (parte inferior de la página) le ayuda a determinar qué resultados pueden esperarse con los argumentos de búsqueda dados. Escribe la dirección del rango en la ventana Inmediato: $B$1:$C$1,$C$2

basic data to test which cells match the criteria

SearchOrder: Filas o Columnas

Puede ser una de las siguientes constantes XlSearchOrder: xlByRows o xlByColumns.

Buscar por columna significa buscar primero en todas las celdas de la columna A, luego en la columna B, etc.
Buscar por fila significa buscar primero en todas las celdas de la fila 1, luego en la fila 2, etc.


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByRows)

SearchDirection: Buscar siguiente

La dirección de búsqueda junto con SearchOrder determinará Cuáles serán las siguientes celdas. La combinación inferior se moverá en columnas hacia arriba, por ejemplo, suponiendo que UsedRange la última celda es B6: B6, B5, B4...


Dim rngFound As Range
Set rngFound = ActiveSheet.UsedRange.Find(What:="x", SearchOrder:=xlByColumns, _
										 SearchDirection:=xlPrevious)

Búsqueda por rangos con criterios múltiples

Para encontrar celdas que coincidan con patrones más complicados puede utilizar una sentencia For Each...Next con:

el operador Like,
Aplicar pruebas booleanas combinadas mediante And, Or, etcétera.

Por ejemplo, el siguiente código busca todas las celdas del rango A1:C5 que utilicen una fuente cuyo nombre empiece por las letras Cour. Cuando Microsoft Excel encuentra una coincidencia, cambia la fuente a Times New Roman.


Dim rngCells As Range
Set rngCells = ActiveSheet.UsedRange.Cells
Dim rngCell As Range
Dim rngFound As Range
For Each rngCell In rngCells 
	If rngCell.Font.Name Like "Cour*" Then
		Set rngFound = rngCell 
		Exit For 
	End If 
Next rngCell

SearchFormat: Formato...

Devuelve celdas sólo con cierto formato aplicado (fuente, color de fondo,...). El siguiente ejemplo muestra que puede encontrar todas las celdas que tienen un color de fondo utilizando una cadena vacía para el parámetro Qué. Descomente . Color = 65535 para obtener las celdas marcadas en amarillo. Alternativamente puede filtrar las celdas por Font.Boldu otras propiedades de la celda.

Nota: Utilizando tanto Qué como SearchFormat encuentra sólo las celdas que coinciden con ambos criterios.


Sub Demo() With Application.FindFormat .Clear ' .Font.Bold = True 
	With .Interior 
		.Pattern = xlSolid '.Color = 65535 'rgbYellow 
	End With 
	ActiveSheet.UsedRange.Find(After:=ActiveCell, What:="", SearchFormat:=True).Select 
	Application.FindFormat.Clear 
End Sub

No lo olvide: Borrar el FindFormat (tanto antes como) después de ejecutar el Find.

MatchByte: por ejemplo, japonés y chino

Sólo se utiliza si ha seleccionado o instalado el soporte de idiomas de doble byte. True para que los caracteres de doble byte coincidan sólo con caracteres de doble byte. Falso para que los caracteres de doble byte coincidan con sus equivalentes de un byte.

Autor: Mark Uildriks

Referencia: Range.Find(Excel) | Microsoft Learn

Haga un bucle sobre las celdas utilizando Find y FindNext para obtener múltiples resultados de búsqueda.

El método Range FindNext

Puede utilizar el método FindNext (o FindPrevious) para repetir la búsqueda comentada anteriormente. FindNext continúa una búsqueda que se inició con el método Find. Encuentra la siguiente celda que coincida con las mismas condiciones y devuelve un objeto Range que representa esa celda.

El código siguiente muestra un bucle que continúa hasta que se completa la búsqueda. Cuando la búsqueda alcanza el final del rango de búsqueda especificado, vuelve al principio del rango. Para detener una búsqueda cuando se produce esta vuelta, guarde la dirección de la primera celda encontrada y, a continuación, compruebe cada dirección de celda encontrada sucesiva con esta dirección guardada.


Dim rngToSearch As Range: Set rngToSearch = ActiveSheet.UsedRange
Dim strWhat As String: strWhat = "test"
Dim rngFound As Range
Set rngFound = rngToSearch.Find(What:=strWhat) 
If Not rngFound Is Nothing Then
	Dim FirstFoundCell As String 
	FirstFoundCell = rngFound.Address 
	Do 
		Debug.Print rngFound.Address
		Set rngFound = rngToSearch.FindNext(After:=rngFound) 
	Loop While FirstFoundCell <> rngFound.Address 
End If

Tratamiento de errores

Antes de continuar con la búsqueda, el código anterior evita el error comprobando que la variable se ha instanciado: If Not rngFound Is Nothing Then. Si esto no se hace, tarde o temprano al aplicar alguno de los procedimientos de la clase, el código se encontrará con el error 91: Object variable or With block variable not set.

El argumento After debe ser una única celda del rango. Si no es así, obtendrá el error 13: Type mismatch.

Añade tratamiento de errores Sobre todo si pretendes que otros usuarios dispongan de tu macro, tiene sentido añadir código de tratamiento de errores.

Un método Range FindAll

No hay un método FindAll incorporado. Dicho esto, basado en el código anterior proporcionamos una función FindAll que devuelve un rango con todas las celdas que coinciden con los criterios de búsqueda y un procedimiento para probar FindAll con diferentes configuraciones, código de abajo.

Biblioteca Code VBA El código también se incluye en el complemento Code VBA Editor, que puede descargarse aquí.


Public Function FindAll(Range As Range, What As Variant, _ 
						Optional LookIn As XlFindLookIn = xlValues, _ 
						Optional LookAt As XlLookAt = xlWhole, _ 
						Optional SearchOrder As XlSearchOrder = xlByRows, _ 
						Optional MatchCase As Boolean = False) As Range
	Dim FoundCell As Range, FirstFound As Range, ResultRange As Range
	Dim LastCell As Range: Set LastCell = Range.Cells(Range.Cells.Count)
	Set FoundCell = Range.Find(What:=Qué, After:=LastCell, LookIn:=LookIn, LookAt:=LookAt, _ 
								SearchOrder:=SearchOrder, MatchCase:=MatchCase) 
	If Not FoundCell Is Nothing Then
		Set FirstFound = FoundCell
		Set ResultRange = FoundCell
		Set FoundCell = Range.FindNext(After:=CeldaEncontrada) 
		Do Until (FoundCell.Address = FirstFound.Address)
			Set ResultRange = Application.Union(ResultRange, FoundCell)
			Set FoundCell = Range.FindNext(After:=CeldaEncontrada) 
		Loop 
	End If
	Set FindAll = ResultRange 
End Function


Sub TestFindAll(What As Variant, _ 
					Optional LookIn As XlFindLookIn = xlValues, _ 
					Optional LookAt As XlLookAt = xlWhole, _ 
					Optional SearchOrder As XlSearchOrder = xlByRows, _ 
					Optional MatchCase As Boolean = False)
	Dim rngUsedRange As Range: Set rngUsedRange = ActiveSheet.UsedRange
	Dim rngSearchResults As Range
	Set rngSearchResults = FindAll(Range:=rngUsedRange, What:=Qué, LookIn:=LookIn, _ 
								LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase) 
	Debug.Print rngSearchResults.Address 
	rngSearchResults.Select 
End Sub