troubleshooting Question

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

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft Excel
6 Comments1 Solution227 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros