# Sorting function not sorting price decimals correctly

Excel vba
this is supposed to sort by numeric.
I have a field with prices
not sorting correctly
doing this:
8.34
8.52
9.34
10.4   <---   out of sequence
9.78
11.53

``````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 vaItems(i, sCol) > vaItems(j, sCol) 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(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) 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 = vaItems(i, c)
'                           vaItems(i, c) = vaItems(j, c)
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(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) 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 = vaItems(i, c)
'                           vaItems(i, c) = vaItems(j, c)
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

End Sub
``````

Thanks
fordraiders
LVL 3
###### Who is Participating?

I believe that CInt performs rounding, not truncation, so your check to see if one cell is greater / less than another cell will fail if both values evaluate to the same integer. In your case, 9.64 will round up to 10, will 10.07 will round down to 10.
0

Author Commented:
aaaah  correct    CDec   will work !
0

Author Commented:
perfect, Right in front of me,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.