Option Compare Text 'la casse est ignorée
Private Sub Worksheet_Change(ByVal Target As Range)
Dim F As Worksheet, r As Range, agent$, ca%, rtt%
Dim c As Range, P As Range, t, n1%, n2%, i%
Set F = Feuil15 'à adapter, CodeName de la feuille Agents
Set r = Intersect(Target, Rows("12:" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For Each r In r 'en cas d'entrées multiples
agent = Replace(Cells(r.Row, 3), "en", "")
ca = 0: rtt = 0
Set c = F.Cells.Find(agent & "CA", , xlValues)
If Not c Is Nothing Then ca = c(, 3)
Set c = F.Cells.Find(agent & "RTT")
If Not c Is Nothing Then rtt = c(, 3)
Set P = Intersect(r.EntireRow, Me.UsedRange)
t = P 'matrice, plus rapide
n1 = 0: n2 = 0
For i = 1 To UBound(t, 2)
If t(1, i) Like "CA*" Then
n1 = n1 + 1
t(1, i) = IIf(n1 > ca, "", "CA" & n1)
ElseIf t(1, i) Like "RTT*" Then
n2 = n2 + 1
t(1, i) = IIf(n2 > rtt, "", "RTT" & n2)
End If
Next
Application.EnableEvents = False: P = t: Application.EnableEvents = True
Next
End Sub