Function TriAlpha(r1, r2)
Dim a, b, vide&, i&, c()
a = r2: b = r1
vide = 2
For i = 1 To UBound(a)
If a(i, 1) = "" Then
vide = vide + 1
a(i, 1) = String(vide, "z")
End If
Next
tri a, b, 1, UBound(a)
ReDim c(1 To UBound(a), 1 To 2)
For i = 1 To UBound(a)
If a(i, 1) Like "zzz*" Then a(i, 1) = ""
c(i, 1) = b(i, 1)
c(i, 2) = a(i, 1)
Next
TriAlpha = c 'matrice
End Function
Sub tri(a, b, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
Do While a(g, 1) < ref: g = g + 1: Loop
Do While ref < a(d, 1): d = d - 1: Loop
If g <= d Then
temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
temp = b(g, 1): b(g, 1) = b(d, 1): b(d, 1) = temp
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call tri(a, b, g, droi)
If gauc < d Then Call tri(a, b, gauc, d)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range
On Error Resume Next
Set P = [A:A].SpecialCells(xlCellTypeConstants, 1).EntireRow
On Error GoTo 0
If P Is Nothing Then Exit Sub
For Each P In P.Areas
If Not Intersect(Target, P) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Union(P(0, 2), P(0, 8), P.Columns(2), P.Columns(8)).Copy P(0, 22) 'avec titres
P.Sort P.Columns(8), xlAscending, Header:=xlNo
P.Resize(, 21).Sort P.Columns(1), xlAscending, Header:=xlNo
Application.EnableEvents = True
End If
Next
End Sub
Sub Classer()
Dim i&, P As Range, j As Byte
Application.ScreenUpdating = False
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 2) = "N°" Then
Set P = Rows(i + 1).Resize(20).Cells
Union(P(0, 2), P(0, 8), P.Columns(2), P.Columns(8)).Copy P(0, 22) 'avec titres
P.Columns(22).Resize(, 2).Sort P.Columns(23), xlAscending, Header:=xlNo
P(1, 36).Resize(2, 3) = "" 'RAZ
For j = 1 To 2 'les 2 premiers
If P(j, 23) <> "" Then
P(j, 36) = P(j, 22)
P(j, 37) = P(j, 23)
P(j, 38) = Application.VLookup(P(j, 36), P.Columns(2).Resize(, 3), 3, 0)
End If
Next j
End If
Next i
End Sub