Private Sub Worksheet_Activate()
Dim tablo, resu(), i&, test As Boolean, deb&, j%, minA#, minO#, n&, k%
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
UsedRange.Delete xlUp 'RAZ
Sheets("LISTE").[A:E].Copy [A1]
With [A1].CurrentRegion
.Sort .Columns(2), xlAscending, .Columns(5), , xlAscending, .Columns(3), xlDescending, Header:=xlYes 'tri sur REFERENCE, COMPATIBILITE et PZIX
tablo = .Value 'matrice, plus raide
ReDim resu(1 To UBound(tablo), 1 To 8)
For i = 2 To UBound(tablo)
test = tablo(i, 2) <> tablo(i - 1, 2) 'test sur REFERENCE
If deb = 0 And test And tablo(i, 5) = "A" Then
deb = i
ElseIf deb And test Then
If tablo(i - 1, 5) = "O" Then
For j = deb To i - 1
If tablo(j, 5) = "A" Then minA = tablo(j, 3)
If tablo(j, 5) = "O" Then minO = tablo(j, 3)
n = n + 1
For k = 1 To 5: resu(n, k) = tablo(j, k): Next k
Next j
resu(n, 6) = minA
resu(n, 7) = minO
resu(n, 8) = minA / minO - 1 'variation
End If
deb = 0
End If
Next i
End With
'---restitution---
[G1:K1] = [A1:E1].Value
[L1] = "MIN PRIX A"
[M1] = "MIN PRIX O"
[N1] = "VAR A/O %"
[O1] = "MOYENNE" & vbLf & "VAR A/O %"
If n Then [G2].Resize(n, 8) = resu: [P1] = Application.Average([N:N])
[O1:P1].Interior.ColorIndex = 6 'jaune
End Sub