Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Sorting a listbox with price and moving 0.00 to bottom of list

Excel 2010 vba

I have some code that I currently use to sort a listbox by a price column.
Call line:  
Run "SortListBox", UserForm2.ListBox32, 11, 1, 1
' this is sorting column 11 , 1 as integer, 1 as ascending by price.


What I need:  I need to keep any prices that have a 0.00 price at the bottom of the sort.

Example:
column1      ......    column 11
4r565                      0.00
2a223                     2.33
3e432                     5.56
3se127                    1.34
3e4877                   11.56
5r4345                    0.00

After code fix:
column1      ......    column 11
3se127                   1.34
2a223                     2.33
3e432                     5.56
3e4877                   11.56
5r4345                    0.00
4r565                      0.00






code below:
Public Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)

    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim C As Integer
    Dim vTemp As Variant
   
    'Put the items in a variant array
  ' On Error GoTo SortListBox_Error
On Error Resume Next
    vaItems = oLb.List
   
    'Sort the Array Alphabetically(1)
    If sType = 1 Then
        For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
            For j = i + 1 To UBound(vaItems, 1)
                'Sort Ascending (1)
                If sDir = 1 Then
                    If IIf(Len(Trim$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, String$(255, Chr$(255)), vaItems(i, sCol)) > _
                       IIf(Len(Trim$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, String$(255, Chr$(255)), vaItems(j, sCol)) Then
                        For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                            vTemp = vaItems(i, C)
                            vaItems(i, C) = vaItems(j, C)
                            vaItems(j, C) = vTemp
                        Next C
                    End If

                'Sort Descending (2)
                ElseIf sDir = 2 Then
                    If vaItems(i, sCol) < vaItems(j, sCol) Then
                        For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                            vTemp = vaItems(i, C)
                            vaItems(i, C) = vaItems(j, C)
                            vaItems(j, C) = vTemp
                        Next C
                    End If
                End If
   
            Next j
        Next i
    'Sort the Array Numerically(2)
    '(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
    ElseIf sType = 2 Then
        For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
            For j = i + 1 To UBound(vaItems, 1)
                'Sort Ascending (1)
                If sDir = 1 Then
                    If CInt(IIf(Len(Trim$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, 32767, vaItems(i, sCol))) > _
                       CInt(IIf(Len(Trim$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, 32767, vaItems(j, sCol))) Then
                        For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                            vTemp = IIf(Len(Trim$(IIf(IsNull(vaItems(i, C)), "", vaItems(i, C)))) = 0, "", vaItems(i, C))
                            vaItems(i, C) = IIf(Len(Trim$(IIf(IsNull(vaItems(j, C)), "", vaItems(j, C)))) = 0, "", vaItems(j, C))
                            vaItems(j, C) = vTemp
                        Next C
                    End If

                'Sort Descending (2)
                ElseIf sDir = 2 Then
                    If CInt(IIf(Len(Trim$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, "0", vaItems(i, sCol))) < _
                       CInt(IIf(Len(Trim$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, "0", vaItems(j, sCol))) Then
                        For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes

                            vTemp = IIf(Len(Trim$(IIf(IsNull(vaItems(i, C)), "", vaItems(i, C)))) = 0, "", vaItems(i, C))
                            vaItems(i, C) = IIf(Len(Trim$(IIf(IsNull(vaItems(j, C)), "", vaItems(j, C)))) = 0, "", vaItems(j, C))
                            vaItems(j, C) = vTemp
                        Next C
                    End If
                End If
   
            Next j
        Next i
    End If
   
    'Set the list to the array
    oLb.List = vaItems
   
  Exit Sub


thanks
fordraiders
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

How about adding a column with a width of zero, placing 99999 in that column when column 11 is zero and then sorting on the new column instead on column 11?
Avatar of Fordraiders

ASKER

ok, i'm using getrows to fill the listbox.

and .columnwidths to make the litbox appear the way i want it to.

how to ad additional column ?
Right click on the control, select Properties, and change 'Column Count'.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
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
Thanks
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014