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
| Private Sub ComboBox1_Click()
Dim F As Worksheet, bd As Variant, i As Integer, temp As Variant
Me.TextBox1.Value = Me.ComboBox1.Text
Me.ListBox1.Clear
Set F = Sheets(Me.TextBox1.Text)
bd = F.Range("F9:J" & F.[F65000].End(xlUp).Row).Value2 ' tableau bd(n,1) pour rapidité
For i = LBound(bd) To UBound(bd)
If bd(i, 1) <> "" Then AddArrayElm(temp, Array(bd(i,1), bd(i,3), bd(i,4), bd(i,5)))
Next i
Call Tri(temp, 0, UBound(temp))
Me.ListBox1.ColumnCount = 4
Me.ListBox1.List = Application.Transpose(temp)
End Sub
Sub Tri(a, gauc, droi) ' Quick sort
Dim ref As String, g As Variant, d As Variant, temp As Variant, i As Integer, n As Integer
n = UBound(a, 1)
ref = a(0, (gauc + droi) \ 2)
g = gauc: d = droi
Do
Do While a(0, g) < ref: g = g + 1: Loop
Do While ref < a(0, d): d = d - 1: Loop
If g <= d Then
For i = 0 To n
temp = a(i, g): a(i, g) = a(i, d): a(i, d) = temp
Next i
g = g + 1: d = d - 1
End If
Loop While g <= d
If g < droi Then Call Tri(a, g, droi)
If gauc < d Then Call Tri(a, gauc, d)
End Sub
Private Sub AddArrayElm(Arr As Variant, Elm As Variant)
Dim i As Long, nRow As Long, nCol As Long
nCol = UBound(Elm)
If Not IsArray(Arr) Then
nRow = 0
ReDim Arr(nCol, nRow)
Else
nRow = UBound(Arr, 2) + 1
ReDim Preserve Arr(nCol, nRow)
End If
For i = 0 To nCol
Arr(i, nRow) = Elm(i)
Next i
End Sub |
Partager