Excel VBA排序列表框数据(如果不是从底部到底部的数字) [英] Excel VBA sort Listbox data if not numeric to bottom

问题描述

我正在使用以下代码对我的列表框数据进行数字排序.我使用以下IF语句检查数据是否为数字(某些数据不是):

I am using the below code to sot my Listbox data numerically. I use the following IF statement to check if data is numeric (some data is not):

If IsNumeric(.List(i, 4)) And IsNumeric(.List(j, 4)) Then

我的列表框数据已正确排序.但是，由于上面的IF语句，排序过程将忽略非数值数据.我需要将非数字数据排序到列表框的底部.

My Listbox data is sorted correctly. However because of the above IF statement the sorting process ignores non-numeric data. I need to sort the non numeric data to the bottom of the Listbox.

Public Sub BubbleSort()

Dim i As Long
Dim j As Long
Dim Temp4 As Variant, Temp3 As Variant, Temp2 As Variant, Temp1 As Variant, Temp0 As Variant
With Plybooks.ListBox1
    For i = 0 To .ListCount - 2
        For j = i + 1 To .ListCount - 1
            If IsNumeric(.List(i, 4)) And IsNumeric(.List(j, 4)) Then
                If CLng(.List(i, 4)) > CLng(.List(j, 4)) Then
                    Temp4 = CLng(.List(j, 4))
                    .List(j, 4) = .List(i, 4)
                    .List(i, 4) = Temp4
                    Temp3 = .List(j, 3)
                    .List(j, 3) = .List(i, 3)
                    .List(i, 3) = Temp3
                    Temp2 = .List(j, 2)
                    .List(j, 2) = .List(i, 2)
                    .List(i, 2) = Temp2
                    Temp1 = .List(j, 1)
                    .List(j, 1) = .List(i, 1)
                    .List(i, 1) = Temp1
                    Temp0 = .List(j, 0)
                    .List(j, 0) = .List(i, 0)
                    .List(i, 0) = Temp0
                End If
            End If
        Next j
    Next i
End With

End Sub

推荐答案

您的列表未正确排序，因为当两个值之一不是数字时，您的排序例程将不执行任何操作.

Your list is not sorted correctly because when one of the two values is not numeric, your sort routine doesn't do anything.

在对不能直接比较的值(对象)列表进行排序时，常见的解决方案是编写一个自定义函数来比较两个值.每次需要比较两个值时，sort函数都会调用此函数.

Common solution when sorting a list of values (objects) that are not directly comparable is to write a custom function that compares two values. The sort function calls this function every time it needs to compare two values.

对于您的情况，该函数可能如下所示:

For your case, the function could look like this:

Function IsLarger(v1 As Variant, v2 As Variant) As Boolean
    
    ' Compare 2 values and return
    '   True if first value is larger
    '   False if second value is larger
    
    ' Comparing:
    ' When v1 and v2 are numeric, compare the values
    ' When only one of those values is numeric, that value is considered a smaller
    ' When both values are non-numeric, make a string compare
    
    If IsNumeric(v1) And IsNumeric(v2) Then
        IsLarger = (Val(v1) > Val(v2))
    ElseIf IsNumeric(v1) Then
        IsLarger = False
    ElseIf IsNumeric(v2) Then
        IsLarger = True
    Else
        IsLarger = (UCase(v1) > UCase(v2))
    End If
            
End Function

您可以简单地将Sort-Routine中的两个嵌套的if语句替换为

You can replace the two nested if-statements in your Sort-Routine simply with

If IsLarger(.List(i, 4), .List(j, 4)) Then

这篇关于Excel VBA排序列表框数据(如果不是从底部到底部的数字)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！