Option Compare Text 'pour ignorer la casse
Sub Concatenate_Color()
Dim r As Range, PlageRech As Range, tablo1, ncol%, tablo2
Dim i&, t$, j%, rech As Variant
Set r = [F3:DA65536].Find("*", , xlValues, , xlByRows, xlPrevious) 'dernière cellule
If r Is Nothing Then Exit Sub
Set PlageRech = Intersect(Sheets("input edms").[D8:D7000], Sheets("input edms").UsedRange)
If PlageRech Is Nothing Then Exit Sub
Set PlageRech = PlageRech.Resize(, 9)
tablo1 = Range("F3:DA" & r.Row) 'un tableau est plus rapide
ncol = UBound(tablo1, 2) 'nombre de colonnes
tablo2 = [DC3].Resize(UBound(tablo1))
For i = 1 To UBound(tablo1)
t = ""
For j = 1 To ncol
If tablo1(i, j) <> "" Then
rech = Application.VLookup(tablo1(i, j), PlageRech, 9, 0)
If Not IsError(rech) Then
If rech = "S" Or rech = "" Then t = t & " - " & tablo1(i, j)
End If
End If
Next
tablo2(i, 1) = Mid(t, 4)
Next
[DC3:DC65536].ClearContents 'RAZ
[DC3].Resize(UBound(tablo1)) = tablo2
End Sub