Re,
Sub RechercheExpressions()
Dim Texp(), blckLst, Saisie, ExpS, exp$, e%, i%, j%, n%
With Worksheets("tableau 1 Saisie CRM")
n = .Cells(.Rows.Count, 1).End(xlUp).Row
If n > 1 Then
Saisie = .Range(.Cells(2, 1), .Cells(n, 4)).Value
Else
MsgBox "Le tableau de saisie est vide !", vbInformation, "Erreur"
Exit Sub
End If
End With
blckLst = [blacklist].Value
For i = 1 To UBound(Saisie)
ExpS = Split(Saisie(i, 1))
For j = 0 To UBound(ExpS)
exp = LCase(Trim(ExpS(j)))
If exp <> "" Then
For n = 1 To UBound(blckLst)
If blckLst(n, 1) = exp Then
ReDim Preserve Texp(4, e)
Texp(0, e) = Saisie(i, 2): Texp(1, e) = Saisie(i, 1)
Texp(2, e) = blckLst(n, 1): Texp(3, e) = Saisie(i, 3)
Texp(4, e) = Saisie(i, 4): e = e + 1
Exit For
End If
Next n
End If
Next j
Next i
With ActiveSheet
Application.ScreenUpdating = False
.Range("A1").CurrentRegion.Offset(1).Clear
If e > 0 Then
With .Range("A2").Resize(e, 5)
.Value = WorksheetFunction.Transpose(Texp)
.Borders.Weight = xlThin
End With
Else
.Range("C2") = "Pas d'espression trouvée"
End If
End With
End Sub
Cordialement.