Correspond à des strings de text similaires, mais pas exactes, dans des projets Excel VBA

Je placerais la macro dans ta section PERSONNELLE, de sorte que la macro est disponible dans toutes les feuilles de calcul. Effectuez ceci en enregistrant une macro fictive et select pour le stocker dans le classur Personal Macro. Vous pouvez maintenant append manuellement de nouvelles macro et fonctions dans ce classur personnel.

Je viens d'essayer celui-ci (je ne connais pas la source d'origine) et ça marche bien.

La formule ressemble à ceci: = PERSONAL.XLSB! FuzzyFind (A1, B $ 1: B $ 20)

Le code est ici:

 Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Fonction FuzzyFind (lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Dim i As Integer, str As Ssortingng, Value As Ssortingng Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Dim a As Integer, b Comme Integer, cell As Variant Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Pour chaque cellule dans tbl_array Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function str = cellule Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Pour i = 1 To Len (lookup_value) Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Si InStr (cell, Mid (lookup_value, i, 1))> 0 Then Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function a = a + 1 Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function cell = Mid (cellule, 1, InStr (cell, Mid (lookup_value, i, 1)) - 1) & Mid (cell, InStr (cell, Mid (lookup_value, i, 1)) + 1, 9999) Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function a = a - Len (cellule) Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Si a> b Ensuite Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function b = a Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Valeur = str Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function a = 0 Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function Cellule suivante Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function FuzzyFind = valeur Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell FuzzyFind = Value End Function 

J'ai utilisé la solution Robert et ça marche bien pour moi. Je publie une solution complète pour les personnes nouvelles pour Excel, mais connais le encoding:

Bien que ce thread soit ancien, mais j'ai pris un code à partir d'un autre thread et j'ai essayé et la solution est de donner une correspondance approximative. Ici, j'essaie de faire correspondre une colonne de feuille1 avec une colonne de feuille2:

1. Ajouter le button de command dans excel
2. mettre le code suivant et cliquer / exécuter le button et la fonction vous donne lieu à la colonne sélectionnée

  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Private Sub CommandButton21_Click ()  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Dim ws As Worksheet  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Dim LRow As Long, i As Long, lval As Ssortingng  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function '~~> Modifiez ceci à la feuille de route pertinente  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Définir ws = ThisWorkbook.Sheets ("Sheet1")  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Avec ws  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function '~~> Trouver la dernière rangée dans Col G qui a des données  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function LRow = .Range ("D" et .Rows.Count) .End (xlUp) .Row  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Si LRow = 1 Then  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function MsgBox "Aucune donnée dans la colonne D"  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Pour i = 2 à LRow  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function lval = "D"  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function .Range ("G" & i) .Value = FuzzyFind (lval & i, .Range ("PWC"))  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Fonction FuzzyFind (lookup_value As Ssortingng, tbl_array As Range) As Ssortingng  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Dim i As Integer, str As Ssortingng, Value As Ssortingng  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Dim a As Integer, b Comme Integer, cell As Variant  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Pour chaque cellule dans tbl_array  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function str = cellule  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Pour i = 1 To Len (lookup_value)  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Si InStr (cell, Mid (lookup_value, i, 1))> 0 Then  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function a = a + 1  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function cell = Mid (cellule, 1, InStr (cell, Mid (lookup_value, i, 1)) - 1) & Mid (cell, InStr (cell, Mid (lookup_value, i, 1)) + 1, 9999)  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function a = a - Len (cellule)  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Si a> b Ensuite  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function b = a  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Valeur = str  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function a = 0  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Cellule suivante  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function Si la valeur <> "" Ensuite  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function FuzzyFind = valeur  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function FuzzyFind = "Aucun"  Private Sub CommandButton21_Click() Dim ws As Worksheet Dim LRow As Long, i As Long, lval As Ssortingng '~~> Change this to the relevant worsheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws '~~> Find Last Row in Col G which has data LRow = .Range("D" & .Rows.Count).End(xlUp).Row If LRow = 1 Then MsgBox "No data in column D" Else For i = 2 To LRow lval = "D" .Range("G" & i).Value = FuzzyFind(lval & i, .Range("PWC")) Next i End If End With End Sub Function FuzzyFind(lookup_value As Ssortingng, tbl_array As Range) As Ssortingng Dim i As Integer, str As Ssortingng, Value As Ssortingng Dim a As Integer, b As Integer, cell As Variant For Each cell In tbl_array str = cell For i = 1 To Len(lookup_value) If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then a = a + 1 cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid (cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999) End If Next i a = a - Len(cell) If a > b Then b = a Value = str End If a = 0 Next cell If Value <> "" Then FuzzyFind = Value Else FuzzyFind = "None" End If End Function 

Vous pouvez searchr Google Excel UDF Fuzzy ou à distance Levensthein. Il y a des UDF flottant autour et Microsoft a également un add-on Fuzzy Lookup / match (quand je l'ai utilisé, il était sujet à une panne et pas intuitif).

Regardez les fonctions de cette publication DDoE . Vous pouvez générer une string de séquence commune la plus longue et comparer la longueur à la string d'origine. Alimentez-en des allumettes connues et des matchs non correspondants et vérifiez si vous pouvez voir une ligne de séparation claire entre eux.

Ces fonctions sont utilisées pour diffuser, ne pas find des correspondances proches, mais elles peuvent fonctionner pour vous.