troubleshooting Question

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

Microsoft Excel
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
Martin Liss
Keep everyone healthy; Get Vaccinated
###### 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.