在排序函数中分配表/标题名称-解网

问：

我有一个代码，可以在手动分配范围时起作用，但这有点麻烦，尤其是当不清楚工作表是否会被修改并因此使代码中的值不正确时。

我的原始代码

Const ProcName As String = "Filter_Ascending_Descending"
On Error GoTo Whoa

Dim WsCP As Worksheet: Set WsCP = Sheets("Cross Platform Database")
Dim WsDND As Worksheet: Set WsDND = Sheets("DO NOT DELETE")
Dim Header As Variant: Header = WsCP.Range("D36").Value
Dim CriteriaOp As Variant: CriteriaOp = WsCP.Range("C38").Value     '<< This will be the Data Value
Dim Criteria As Variant: Criteria = WsCP.Range("D38").Value         '<< This will be the Data Value
Dim Order As Variant: Order = WsCP.Range("C36").Value               '<< Expected to be Ascending/Descending
Dim OrderHeader As Variant: OrderHeader = "Table1[[#Headers],[Sales]]"

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'This code will sort the data in the relevant column according to the user selection
    If WsDP.Range("C26") = "Ascending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlAscending, Header:=xlYes

    ElseIf WsDP.Range("C26") = "Descending" Then
        Sheets("Cross Platform Database").Range("S24:S71499").Sort _
          Key1:=Sheets("Cross Platform Database").Range("S23"), Order1:=xlDescending, Header:=xlYes
    Else
        
    End If

SafeExit:
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
    Exit Sub

Whoa:
    Debug.Print "'" & ProcName & "' Run-time error '" _
      & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume SafeExit

End Sub

我试图将所有范围更改为表名，但我的代码跳转到错误并返回

错误 5“无效的过程调用或参数”

'This code will sort the data in the relevant column according to the user selection
If Order = "Ascending" Then
    WsCP.ListObjects("Table1").Range("Table1[Sales]").Sort _
      Key1:=WsCP.Range(OrderHeader), Order1:=xlAscending, Header:=xlYes

Excel VBA 排序 excel-tables

对 Excel 表格进行排序

Option Explicit

Sub TestSortTable()

    Dim WsCP As Worksheet: Set WsCP = Worksheets("Cross Platform Database")
    Dim AscDesc As String: AscDesc = CStr(WsCP.Range("C26").Value) ' unclear
    
    Dim tbl As ListObject: Set tbl = WsCP.ListObjects("Table1")
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Sales")
    
    Select Case LCase(AscDesc)
    Case "ascending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlAscending, Header:=xlYes
    Case "descending"
        tbl.Range.Sort _
            Key1:=lcl.Range, Order1:=xlDescending, Header:=xlYes
    Case Else ' do nothing
    End Select

End Sub

上一个：自动筛选错误：调用的对象已与其客户端断开连接

下一个：引用筛选列中的顶部单元格

在排序函数中分配表/标题名称

Assigning Table/Header names in Sort Functions

评论

对 Excel 表格进行排序