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