Link to home
Start Free TrialLog in
Avatar of tiehaze
tiehaze

asked on

Using Bubblesort to sort numbers

I have the following code, and it is sorting numbers as if they were text. For example:

12
2
399922
4000
5

I need it to sort based on the value:
2
5
12
4000
399922

Here is my code, can someone show me how i can get it to work correctly:

Private Sub CommandButton3Sort_Click()
'http://www.dailydoseofexcel.com/archives/2004/05/24/sorting-a-multicolumn-listbox/
Dim i As Long, j As Long, LbList As Variant, K As Long
Dim sTemp As String, sTemp2 As String, sTemp3 As String, sTemp4 As String, sTemp5 As String, sTemp6 As String, sTemp7 As String
    'Store the list in an array for sorting
    LbList = Me.ListBox3.List
    K = ComboBox3.ListIndex
    'Bubble sort the array on the first value
    For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
        For j = i + 1 To UBound(LbList, 1)
            If LbList(i, K) > LbList(j, K) Then
                'Swap the first value
                sTemp = LbList(i, 0)
                LbList(i, 0) = LbList(j, 0)
                LbList(j, 0) = sTemp
               
                'Swap the second value
                sTemp2 = LbList(i, 1)
                LbList(i, 1) = LbList(j, 1)
                LbList(j, 1) = sTemp2
               
                'Swap the third value
                sTemp3 = LbList(i, 2)
                LbList(i, 2) = LbList(j, 2)
                LbList(j, 2) = sTemp3
               
                'Swap the fourth value
                sTemp4 = LbList(i, 3)
                LbList(i, 3) = LbList(j, 3)
                LbList(j, 3) = sTemp4
               
                'Swap the fifth value
                sTemp5 = LbList(i, 4)
                LbList(i, 4) = LbList(j, 4)
                LbList(j, 4) = sTemp5
               
                'Swap the sixth value
                sTemp6 = LbList(i, 5)
                LbList(i, 5) = LbList(j, 5)
                LbList(j, 5) = sTemp6
               
                'Swap the seventh value
                sTemp7 = LbList(i, 6)
                LbList(i, 6) = LbList(j, 6)
                LbList(j, 6) = sTemp7
            End If
        Next j
    Next i
    'Remove the contents of the listbox
    Me.ListBox3.Clear
    'Repopulate with the sorted list
    Me.ListBox3.List = LbList
End Sub

Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Try this:

Private Sub CommandButton3Sort_Click()
'http://www.dailydoseofexcel.com/archives/2004/05/24/sorting-a-multicolumn-listbox/
Dim i As Long, j As Long, LbList As Variant, K As Long
Dim sTemp As String, sTemp2 As String, sTemp3 As String, sTemp4 As String, sTemp5 As String, sTemp6 As String, sTemp7 As String
    'Store the list in an array for sorting
    LbList = Me.ListBox3.List
    K = ComboBox3.ListIndex
    'Bubble sort the array on the first value
    For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
        For j = i + 1 To UBound(LbList, 1)
            If CLng(LbList(i, K)) > CLng(LbList(j, K)) Then
                'Swap the first value
                sTemp = LbList(i, 0)
                LbList(i, 0) = LbList(j, 0)
                LbList(j, 0) = sTemp
               
                'Swap the second value
                sTemp2 = LbList(i, 1)
                LbList(i, 1) = LbList(j, 1)
                LbList(j, 1) = sTemp2
               
                'Swap the third value
                sTemp3 = LbList(i, 2)
                LbList(i, 2) = LbList(j, 2)
                LbList(j, 2) = sTemp3
               
                'Swap the fourth value
                sTemp4 = LbList(i, 3)
                LbList(i, 3) = LbList(j, 3)
                LbList(j, 3) = sTemp4
               
                'Swap the fifth value
                sTemp5 = LbList(i, 4)
                LbList(i, 4) = LbList(j, 4)
                LbList(j, 4) = sTemp5
               
                'Swap the sixth value
                sTemp6 = LbList(i, 5)
                LbList(i, 5) = LbList(j, 5)
                LbList(j, 5) = sTemp6
               
                'Swap the seventh value
                sTemp7 = LbList(i, 6)
                LbList(i, 6) = LbList(j, 6)
                LbList(j, 6) = sTemp7
            End If
        Next j
    Next i
    'Remove the contents of the listbox
    Me.ListBox3.Clear
    'Repopulate with the sorted list
    Me.ListBox3.List = LbList
End Sub

Kevin
Avatar of tiehaze
tiehaze

ASKER

Works perfectly, but some of the columns are text, which doesn't work. Any ideas how to fix that.
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tiehaze

ASKER

Beautiful... thanks man