Solved

Sorting function not sorting price decimals correctly

Posted on 2014-03-20
3
124 Views
Last Modified: 2014-03-20
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




price not sorting right


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

Open in new window



Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39943496
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39943552
aaaah  correct    CDec   will work !
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39943568
perfect, Right in front of me,
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA error subscript out of range help 5 29
Excel -  lookup two values and return a value 3 30
Excel - merge column values 3 29
VBA Lookup Problem 2 15
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question