时间戳

首页 > 解决方案 > 尝试使用 VBA 对工作表范围进行排序 - 升序有效但不降序

excel - 尝试使用 VBA 对工作表范围进行排序 - 升序有效但不降序

问题描述

我在从“A6”到“E11”的工作表中有一系列培训课程。参加课程的工作人员列于 F 至 N 栏，他们的姓名在第 6 行，他们的出勤率在相关单元格中用“X”表示。btnOrder课程日期在 C 列中。我在工作表上附加了一个按钮。按钮的标题从“升序排序”切换到“降序排序”。我正在使用以下代码。代码可以很好地对“升序”进行排序，但无论代码的哪一部分运行，数据总是按“升序”排序。谁能看到我做错了什么？

Private Sub btnOrder_Click()
    Dim varColumnLetter As String
    Dim varLastRow As Integer

    Application.ScreenUpdating = False
    
    If btnOrder.Caption = "Sort Ascending" Then
        With Worksheets("External Training Matrix").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("C6:C11"), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A6:N11")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        MsgBox "Courses sorted successfully into ascending order by course date, oldest courses at the top", vbOKOnly + vbInformation, "Success"
        btnOrder.Caption = "Sort Descending"
        
    ElseIf btnOrder.Caption = "Sort Descending" Then
        With Worksheets("External Training Matrix").Sort
            .SortFields.Add Key:=Range("C6:C11"), _
                SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .SetRange Range("A6:N11")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
        MsgBox "Courses sorted successfully into descending order by course date, newest courses at the top", vbOKOnly + vbInformation, "Success"
        btnOrder.Caption = "Sort Ascending"
    End If
    
    ActiveSheet.Range("A6").Select
    
    Application.ScreenUpdating = True
End Sub

标签： excelvbasorting

解决方案

你可以尝试这样的事情：

Sub SortData(bl_Ascending As Boolean, str_SortColumn As String, lng_SortRow_Start As Long, lng_SortRow_End As Long)

Dim xl_SortOrder As XlSortOrder


If bl_Ascending = True Then
    xl_SortOrder = xlAscending
Else
    xl_SortOrder = xlDescending
End If

With ThisWorkbook.Worksheets("External Training Matrix").Sort
    .SortFields.Clear ' clear previous sorting

    .SortFields.Add Key:=Range(str_SortColumn & lng_SortRow_Start & ":" & str_SortColumn & lng_SortRow_End), _
        SortOn:=xlSortOnValues, _
        Order:=xl_SortOrder, _
        DataOption:=xlSortNormal
    .SetRange Range("A" & lng_SortRow_Start & ":" & "N" & lng_SortRow_End)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

然后像这样调用：

SortData False, "G", 6, 11

我建议您使行和数据范围动态化。但我相信您可以将其作为下一步

我已经用数字测试了代码并且它有效

推荐阅读