Encuentre un valor en una columna usando Excel VBA

1. Encuentre una cadena/valor en una columna usando la función Find() en VBA
2. Encuentre una cadena/valor en una columna usando la función Match() en VBA
3. Encuentre una cadena/valor en una columna usando bucles en VBA
4. Encontrar una cadena en varias columnas usando bucles en VBA

La creación de herramientas de automatización en Excel requiere varias funciones de coincidencia de cadenas como Instr(), CStr(), Split(), etc. Estas funciones encuentran una subcadena dentro de una cadena. Sin embargo, cuando se trata de cadenas/valores a través de columnas, no puede usar estas funciones ya que son funciones de una sola cadena.

Este artículo demostrará tres técnicas para encontrar una cadena o un valor en una columna. El valor de retorno es el número de fila donde se encuentra la cadena de destino.

Técnicas de búsqueda de cadenas:

1. Usando la función Find().
2. Usando la función Match.
3. Uso de bucles

Para todo el artículo, esta hoja virtual será la hoja de referencia.

Valores de la hoja 1:

  |   A     | B |       C      |  D  |
1 | Apple   | 4 | Philippines  |  3  |
2 | Orange  | 3 | Mexico       |  5  |
3 | Banana  | 6 | Thailand     |  3  |
4 | Carrot  | 7 | Saudi Arabia |  7  |
5 | Grapes  | 5 | U.S.A.       |  1  |
6 | Pear    | 3 | Japan        |  0  |
7 | Citrus  | 9 | Mexico       |  3  |
8 | Pomelo  | 2 | Taiwan       |  4  |
9 |         | 3 | Singapore    |  7  |
10|         | 4 | Mexico       |  8  |

Encuentre una cadena/valor en una columna usando la función Find() en VBA

Sintaxis:

Range.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase)

Parámetros:

El bloque de código a continuación demostrará el uso de la función Find() para devolver la fila de la cadena para buscar.

Function SearchStr(str As String) As Range

Dim wb As Workbook
Dim s1 As Worksheet
Dim rng As Range

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

Set rng = s1.Columns("A:A")
Set SearchStr = rng.Find(What:=str, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

End Function

Sub BananaSearch()

If SearchStr("Banana") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Banana").Row

End If

End Sub

Sub MelonSearch()

If SearchStr("Melon") Is Nothing Then
    Debug.Print "Not in range."

Else
    Debug.Print "Found at row: " & SearchStr("Melon").Row

End If
End Sub

Salida BananaSearch:

Found at row: 3

Salida MelonSearch:

Not in range

Encuentre una cadena/valor en una columna usando la función Match() en VBA

La principal diferencia entre las funciones Find() y Match() es que la primera devuelve el objeto Range donde se encuentra la cadena, mientras que la segunda devuelve la posición donde la cadena tiene una coincidencia.

Sintaxis:

Application.Match([StringtoSearch],[RangetoSearchIn],[MatchType])

Parámetros:

Observaciones:

Para los argumentos [MatchType], estos son los valores:

El bloque de código a continuación demostrará el uso de la función Match() para devolver la fila de la cadena para buscar.

Function SearchNum(IntToSearch As Integer) As Variant

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")

If Not IsError(Application.Match(IntToSearch, s1.Columns("B:B"), 0)) Then
    SearchNum = Application.Match(IntToSearch, s1.Columns("B:B"), 0)

Else
    SearchNum = "Not Found"
End If

End Function

Sub LookForSix()

Debug.Print "A match is located at row " & SearchNum(6)

End Sub

Sub LookForZero()

Debug.Print "A match is located at row " & SearchNum(0)

End Sub

Salida LookForSix:

A match is located at row 3

Salida LookForZero:

A match is located at row Not Found

Encuentre una cadena/valor en una columna usando bucles en VBA

A diferencia de las funciones Find() y Match(), el uso de bucles permite al usuario ejecutar el código. Una ventaja de los bucles es que podemos devolver todas las coincidencias de un Rango. Sin embargo, no se recomiendan los bucles cuando se trata de datos de gran tamaño, ya que itera con cada celda dentro de los límites especificados.

El bloque de código a continuación demostrará el uso de bucles para devolver la fila de la cadena para buscar.

Function GetAllRows(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")
Dim i As Long
Dim lrow As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

For i = 1 To lrow
    
    If s1.Cells(i, 3) = str Then
        'Add row number in the arraylist
        coll.Add (i)
        
    End If
    
Next i

Set GetAllRows = coll

End Function

Sub FindForMexico()

Dim coll2 As Object

Set coll2 = GetAllRows("Mexico")

Dim j As Integer

For j = 0 To coll2.Count - 1

    Debug.Print "A match is found in row: " & coll2(j)

Next j

End Sub

Salida FindforMexico:

A match is found in row: 2
A match is found in row: 7
A match is found in row: 10

Encontrar una cadena en varias columnas usando bucles en VBA

Los tres bloques de código anteriores fueron diseñados para buscar en una sola columna. En raras ocasiones, debe buscar coincidencias de cadenas en varias columnas o incluso en toda la hoja de trabajo. Pero si lo necesita, el bloque de código a continuación lo ayudará.

Function GetAllRowsFromSheet(str As String) As Object

Dim wb As Workbook
Dim s1 As Worksheet
Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")

'Create row numbers(i,j)  and last row/column variable in long datatypes.
Dim i, j As Long
Dim lrow, lcol As Long

lrow = s1.Cells(s1.Rows.Count, 3).End(xlUp).Row

lcol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

For j = 1 To lcol
    For i = 1 To lrow
        
        If s1.Cells(i, j) = str Then
            coll.Add ("Row :" & i & " Column : " & j)
        End If 
    Next i
Next j

Set GetAllRowsFromSheet = coll

End Function

Sub FindFor3()

Dim coll2 As Object

Set coll2 = GetAllRowsFromSheet(3)

Dim j As Integer

For j = 0 To coll2.Count - 1

Debug.Print "A match is found in " & coll2(j)

Next j

End Sub

Salida Lookfor3:

A match is found in Row :2 Column : 2
A match is found in Row :6 Column : 2
A match is found in Row :9 Column : 2
A match is found in Row :1 Column : 4
A match is found in Row :3 Column : 4
A match is found in Row :7 Column : 4