Sub MAJ()
Dim S As Range, nlig&, D As Range, dest, i&, nom$, source, j&, but As Byte, maxi&, compte&
Set S = [A1].CurrentRegion
nlig = S.Rows.Count
If nlig = 1 Then Exit Sub
Set D = [sequence_2010_2011] 'tableau structuré
dest = D 'matrice, plus rapide
Application.ScreenUpdating = False
S.Columns(2).Insert xlToRight 'insère une colonne auxiliaire
For i = 1 To UBound(dest)
nom = dest(i, 1)
S.Columns(2).ClearContents 'RAZ de la colonne auxiliaire
source = S.Value2 'matrice, plus rapide
For j = 2 To nlig
If source(j, 4) = nom Or source(j, 5) = nom Then source(j, 2) = source(j, 3) 'copie la date
Next j
S.Columns(2) = Application.Index(source, , 2) 'restitue les dates
S.Sort S(1, 2), xlAscending, Header:=xlYes 'tri sur la colonne auxiliaire
source = S.Value2 'matrice, plus rapide
'---colonnes 2 à 6---
For but = 1 To 5
maxi = 0
For j = 1 To nlig
If source(j, 2) = "" Then Exit For
If source(j, 8) <> but Then compte = 0 Else _
compte = compte + 1: If compte > maxi Then maxi = compte
Next j
dest(i, but + 1) = maxi
Next but
'---colonne 7---
maxi = 0
For j = 1 To nlig
If source(j, 2) = "" Then Exit For
If source(j, 8) <> 0 And source(j, 8) <> 1 Then compte = 0 Else _
compte = compte + 1: If compte > maxi Then maxi = compte
Next j
dest(i, 7) = maxi
'---colonne 8---
maxi = 0
For j = 1 To nlig
If source(j, 2) = "" Then Exit For
If source(j, 8) <> 2 And source(j, 8) <> 3 Then compte = 0 Else _
compte = compte + 1: If compte > maxi Then maxi = compte
Next j
dest(i, 8) = maxi
'---colonne 9---
maxi = 0
For j = 1 To nlig
If source(j, 2) = "" Then Exit For
If Val(source(j, 8)) < 4 Then compte = 0 Else _
compte = compte + 1: If compte > maxi Then maxi = compte
Next j
dest(i, 9) = maxi
Next i
'---restitution---
S.Columns(2).Delete xlToLeft 'supprime la colonne auxiliaire
S.Sort S(1, 2), xlAscending, Header:=xlYes 'ordre initial
D = dest
End Sub
