1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
| Option Explicit
Sub Initial()
Const Alpha As String = "ALPHA"
Const Beta As String = "BETA"
Dim LastLig As Long, i As Long, a As Long, b As Long
Dim Tb, TbA(), TbB()
With Worksheets("Feuil1")
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
a = Application.CountIf(.Range("B2:B" & LastLig), Alpha)
b = Application.CountIf(.Range("B2:B" & LastLig), Beta)
Tb = .Range("A2:D" & LastLig)
ReDim TbA(1 To a, 1 To 1)
ReDim TbB(1 To b, 1 To 1)
a = 0
b = 0
For i = 1 To UBound(Tb, 1)
If UCase(Tb(i, 2)) = Alpha Then
a = a + 1
Remplir TbA, a, Tb, i
ElseIf UCase(Tb(i, 2)) = Beta Then
b = b + 1
Remplir TbB, b, Tb, i
End If
Next i
TriRapide TbA, 1, a
TriRapide TbB, 1, b
Resultat TbA
Resultat TbB
.Range("F2").Resize(a, 1) = TbA
.Range("G2").Resize(b, 1) = TbB
End With
End Sub
Private Sub Remplir(ByRef TbX, ByVal x As Long, ByVal Tb, ByVal i As Long)
TbX(x, 1) = Tb(i, 3) & "µ" & Tb(i, 4) & "µ" & Tb(i, 2) & "µ" & Tb(i, 1)
End Sub
Private Sub TriRapide(ByRef Tb, ByVal Bas As Long, ByVal Haut As Long)
Dim Md As String, Tmp As String
Dim m As Long, n As Long
m = Bas
n = Haut
Md = Tb((Bas + Haut) \ 2, 1)
Do While m <= n
Do While Tb(m, 1) < Md And m < Haut
m = m + 1
Loop
Do While Md < Tb(n, 1) And n > Bas
n = n - 1
Loop
If m <= n Then
Tmp = Tb(m, 1)
Tb(m, 1) = Tb(n, 1)
Tb(n, 1) = Tmp
m = m + 1
n = n - 1
End If
Loop
If Bas < n Then TriRapide Tb, Bas, n
If m < Haut Then TriRapide Tb, m, Haut
End Sub
Private Sub Resultat(ByRef TbX)
Dim i As Long
For i = 1 To UBound(TbX, 1)
TbX(i, 1) = Split(TbX(i, 1), "µ")(3)
Next i
End Sub |
Partager