Bonjour à tous ,
Une autre version. Les résultats sont sur la feuille "Result".
Cliquer sur le bouton Hop! de la feuille "Result".
Le code dans module1 :
Sub Test()
Dim der&, t, v, i&, k&
With Sheets("Feuil1")
der = Application.Max(Application.IfError(Application.Match(9 ^ 99, .Columns(1)), 0), Application.IfError(Application.Match(String(255, "z"), .Columns(1)), 0))
t = .Range("A1:A" & der)
End With
With Sheets("Feuil2")
der = Application.Max(Application.IfError(Application.Match(9 ^ 99, .Columns(1)), 0), Application.IfError(Application.Match(String(255, "z"), .Columns(1)), 0))
v = .Range("A1:F" & der)
End With
For i = 1 To UBound(v)
v(i, 1) = Trim(v(i, 1))
If v(i, 1) <> "" Then
'v(i, 1) = LCase(Replace(v(i, 1), " ", ""))
For k = 1 To UBound(t)
If LCase(t(k, 1)) Like "*" & LCase(v(i, 1)) & "*" Then
v(i, 1) = t(k, 1): Exit For
End If
Next k
End If
Next i
With Sheets("Result")
.Columns("a:f").Clear
.Range("A1").Resize(UBound(v), UBound(v, 2)) = v
.Range("A:F").EntireColumn.AutoFit
End With
End Sub