excel - 2016年记录的排序生成 "Error 438. Object doesn' t support this property or method"in 2010

Question

我录制了一个用于 2 列排序的宏，该宏在 Excel 2016 上有效。

我在 Excel 2010 上收到以下错误。

Error 438. Object doesn't support this property or method.

2010 版 Excel 不接受什么？

Sub SortOpenOrder()
'
' Macro1 Macro
'

'
    Range("Table2[[#Headers],[Customer No.]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Open Orders").ListObjects("Table2").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Open Orders").ListObjects("Table2").Sort.SortFields. _
        Add2 Key:=Range("Table2[Customer No.]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Open Orders").ListObjects("Table2").Sort.SortFields. _
        Add2 Key:=Range("Table2[PO '#]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Open Orders").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Table2[[#Headers],[Customer No.]]").Select
End Sub

Answer 1

您拥有的代码看起来不会在任一版本的 Excel 上崩溃，但重构它以获得更好的编码实践肯定会有所帮助(正如 fabio 提到的那样)。

试试这个:

Dim myTable as ListObject
Set myTable = ThisWorkbook.Worksheets("Open Orders").ListObjects("Table2")

With myTable.Sort

    With .SortFields
         .Clear
         .Add Key:=myTable.ListColumns("Customer No.").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .Add Key:=myTable.ListColumns("PO '#").DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With

   .Header = xlYes
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply

End With