excel vba在列表视图上排序整型值

eoigrqb6  于 2022-12-14  发布在  其他
关注(0)|答案(2)|浏览(126)

我需要按int值对值进行排序,例如1 2 3 10 1000
但是我代码就像字符串一样工作,比如11010023
下面是我的EXCEL VBA排序代码:

Private Sub lstview1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

With lstview1
    .SortKey = ColumnHeader.Index - 1
    If .SortOrder = lvwAscending Then
        .SortOrder = lvwDescending
    Else
        .SortOrder = lvwAscending
    End If
    .Sorted = True
End With

结束子组件
我怎么能解决这个Excel vba

a9wyjsp7

a9wyjsp71#

Listview是按字母顺序排序的,这是它的一个局限性。下面是我为你创建的一个快速的例子来排序数字数据。

代码

Option Explicit

Dim i As Long

Private Sub UserForm_Initialize()
    With ListView1
        .View = lvwReport
        
        .ColumnHeaders.Add , , "Number", 50
        .ColumnHeaders.Add , , "Fruit", 50
        
        For i = 1000 To 1 Step -1
            .ListItems.Add(, , i).SubItems(1) = "Fruit" & i
        Next
    End With
End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    With ListView1
        If ColumnHeader.Index = 1 Then
            SortDataWithNumbers
        Else
            .Sorted = True
        End If
    End With
End Sub

Sub SortDataWithNumbers()
    Dim sTemp As String * 10
    Dim lvCount As Long
    
    With ListView1
        lvCount = .ListItems.Count
        
        For i = 1 To lvCount
            sTemp = vbNullString
            
            If .SortKey Then
                'RSet - right align a string within a string variable.
                RSet sTemp = .ListItems(i).SubItems(.SortKey)
                .ListItems(i).SubItems(.SortKey) = sTemp
            Else
                RSet sTemp = .ListItems(i)
                .ListItems(i).Text = sTemp
            End If
        Next
        
        .Sorted = True
        
        For i = 1 To lvCount
            If .SortKey Then
                .ListItems(i).SubItems(.SortKey) = _
                LTrim$(.ListItems(i).SubItems(.SortKey))
            Else
                .ListItems(i).Text = LTrim$(.ListItems(i))
            End If
        Next
    End With
End Sub

行动中

yhxst69z

yhxst69z2#

Siddharth Rout的代码工作得很好,但是如果你希望能够对所有列进行排序并且其中一些是数值型的,你可以使用ColumnHeader.Index,并与下面的代码(源代码和致谢名单:(https://www.tek-tips.com/viewthread.cfm?qid=578008):

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
With ListView1
    .Sorted = False
    If .SortKey <> ColumnHeader.Index - 1 Then
        .SortKey = ColumnHeader.Index - 1
    Else
        .SortOrder = 1 - .SortOrder
    End If
    
    'In the following line you specify the columns with numeric values
    If ColumnHeader.Index = 1 Or ColumnHeader.Index = 7 Then 
        Dim S As String * 10, N As Integer
        'justify the text using padding spaces
        For N = 1 To .ListItems.Count
            S = vbNullString
            If .SortKey Then
                RSet S = .ListItems(N).SubItems(.SortKey)
                .ListItems(N).SubItems(.SortKey) = S
            Else
                RSet S = .ListItems(N)
                .ListItems(N).Text = S
            End If
        Next
        'sort column using "justified" text
        .Sorted = True
        'trim spaces from the text
        For N = 1 To .ListItems.Count
            If .SortKey Then
                .ListItems(N).SubItems(.SortKey) = _
                LTrim$(.ListItems(N).SubItems(.SortKey))
            Else
                .ListItems(N).Text = LTrim$(.ListItems(N))
            End If
        Next
    Else
        .Sorted = True
    End If
End With
End Sub

相关问题