Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim P As Range, colcea%, coltype%, colmois%, colan%
Dim coltaux%, colpond%, ncol%, t, i&, a#, b#, c#, x$, y$, z$
'---préparation---
Set P = [B2].CurrentRegion 'adapter éventuellement
colcea = 1: coltype = 2: colmois = 3: colan = 4: coltaux = 5: colpond = 6
ncol = P.Columns.Count + 1 'une colonne auxiliaire en plus pour le tri
Set P = P.Resize(P.Rows.Count + 2, ncol) '2 lignes en plus par sécurité
Application.ScreenUpdating = False
P(1, ncol) = 1
P.Columns(ncol).DataSeries 'numérotation des lignes
P.Sort P(1, colan), , P(1, colmois), , , P(1, colcea), Header:=xlYes 'tri sur Année+Mois+CEA
t = P 'matrice, plus rapide
'---remplissage de la colonne colpond---
For i = 2 To UBound(t) - 2 Step 3
If IsNumeric(t(i, coltaux)) Then a = t(i, coltaux) Else a = 0
If IsNumeric(t(i + 1, coltaux)) Then b = t(i + 1, coltaux) Else b = 0
If IsNumeric(t(i + 2, coltaux)) Then c = t(i + 2, coltaux) Else c = 0
x = t(i, coltype): y = t(i + 1, coltype): z = t(i + 2, coltype)
If t(i, coltaux) = "NA" Or t(i + 1, coltaux) = "NA" Or t(i + 2, coltaux) = "NA" Then
t(i, colpond) = a * IIf(x = "Dossier", 0.8, IIf(x = "Voix", 0, 0.2))
t(i + 1, colpond) = b * IIf(y = "Dossier", 0.8, IIf(y = "Voix", 0, 0.2))
t(i + 2, colpond) = c * IIf(z = "Dossier", 0.8, IIf(z = "Voix", 0, 0.2))
Else
t(i, colpond) = a * IIf(x = "Dossier", 0.45, IIf(x = "Voix", 0.45, 0.1))
t(i + 1, colpond) = b * IIf(y = "Dossier", 0.45, IIf(y = "Voix", 0.45, 0.1))
t(i + 2, colpond) = c * IIf(z = "Dossier", 0.45, IIf(z = "Voix", 0.45, 0.1))
End If
Next
'---restitution---
P.Columns(colpond).Resize(UBound(t) - 2) = Application.Index(t, , colpond)
P.Sort P(1, ncol), xlAscending 'tri pour remettre dans l'ordre initial
P.Columns(ncol).ClearContents
End Sub