Trouver une valeur dans une colonne à l'aide d'Excel VBA

1. Rechercher une chaîne/valeur dans une colonne à l’aide de la fonction Rechercher() dans VBA
2. Trouver une chaîne/valeur dans une colonne à l’aide de la fonction Match() dans VBA
3. Trouver une chaîne/valeur dans une colonne à l’aide de boucles dans VBA
4. Recherche d’une chaîne dans plusieurs colonnes à l’aide de boucles dans VBA

La création d’outils d’automatisation dans Excel nécessite plusieurs fonctions de correspondance de chaînes telles que Instr(), CStr(), Split(), etc. Ces fonctions trouvent une sous-chaîne dans une chaîne. Cependant, lorsque vous traitez des chaînes/valeurs via des colonnes, vous ne pouvez pas utiliser ces fonctions car il s’agit de fonctions à chaîne unique.

Cet article présente trois techniques pour rechercher une chaîne ou une valeur dans une colonne. La valeur return est le numéro de ligne où se trouve la chaîne cible.

Techniques de recherche de chaînes :

1. Utilisation de la fonction Rechercher()
2. Utilisation de la fonction Match
3. Utiliser des boucles

Pour tout l’article, cette fiche virtuelle sera la fiche de référence.

Valeurs de la feuille 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  |

Rechercher une chaîne/valeur dans une colonne à l’aide de la fonction Rechercher() dans VBA

Syntaxe:

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

Paramètres:

Le bloc de code ci-dessous illustrera l’utilisation de la fonction Find() pour renvoyer la ligne de la chaîne à rechercher.

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

Sortie BananeSearch :

Found at row: 3

Sortie MelonSearch :

Not in range

Trouver une chaîne/valeur dans une colonne à l’aide de la fonction Match() dans VBA

La principale différence entre les fonctions Find() et Match() est que la première renvoie l’objet Range où la chaîne est trouvée tandis que la seconde renvoie la position où la chaîne a une correspondance.

Syntaxe:

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

Paramètres:

Remarques:

Pour les arguments [MatchType], voici les valeurs :

Le bloc de code ci-dessous illustrera l’utilisation de la fonction Match() pour renvoyer la ligne de la chaîne à rechercher.

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

Sortie LookForSix :

A match is located at row 3

Sortie LookForZero :

A match is located at row Not Found

Trouver une chaîne/valeur dans une colonne à l’aide de boucles dans VBA

Contrairement aux fonctions Find() et Match(), l’utilisation de boucles permet à l’utilisateur d’exécuter le code. L’un des avantages des boucles est que nous pouvons renvoyer toutes les correspondances d’une plage. Cependant, les boucles ne sont pas recommandées lorsqu’il s’agit de données volumineuses car elles itèrent avec chaque cellule dans les limites spécifiées.

Le bloc de code ci-dessous illustrera l’utilisation de boucles pour renvoyer la ligne de la chaîne à rechercher.

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

Sortie FindforMexico :

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

Recherche d’une chaîne dans plusieurs colonnes à l’aide de boucles dans VBA

Les trois blocs de code ci-dessus ont été conçus pour examiner une seule colonne. Rarement, vous devez rechercher des correspondances de chaînes dans plusieurs colonnes ou même dans l’ensemble de la feuille de calcul. Mais si vous en avez besoin, le bloc de code ci-dessous vous aidera.

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

Sortie 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