在VBA中排序多个键;运行时错误450：参数数量错误或属性分配无效 [英] Sorting Multiple Keys in VBA; Runtime Error 450: Wrong number of arguments or invalid property assignment

问题描述

我一直在写这个VBA代码，以帮助我排序我所拥有的信息数据库（名称，发票号码，地址等）。我最近学习了如何在VBA中排序，而不选择工作表中的实际范围，并且我尝试使用多个键（key1，key2，key3）进行排序，但没有运气。我继续得到运行时错误450：运行此代码时参数数量或参数无效。我想要能够多次对一系列数据进行排序，因此，例如通过列F排序范围（A：K），然后按E排序，然后按B等排序。

I have been writing this VBA code to help me sort a database of information I have (Name, Invoice#, Address, etc). I recently learned how to sort in VBA without selecting the actual range in the worksheet and with that I tried sorting with multiple keys (key1, key2, key3), but no luck. I keep getting a Run time error 450: Wrong number of arguments or invalid property assignment when running this code. I want to be able to sort a range of data multiple times, so for instance sort a range (A:K) by column F, then by E, then by B etc.

以下是我用于排序列的代码：

Here is the code I have been using for sorting my columns:

ActiveSheet.Range("A:K").Sort _
Key1:=ActiveSheet.Range("F2"), Order1:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
Key2:=ActiveSheet.Range("E2"), Order2:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption2:=xlSortNormal, _
Key3:=ActiveSheet.Range("D2"), Order3:=xlAscending, HEADER:=xlYes, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption3:=xlSortNormal

这很奇怪，因为起初我尝试运行前2个键（key1，key2），它工作得很好，然后我复制并粘贴下来创建下一个键（key3），并出现错误。当我尝试调试时，上述所有代码都会突出显示。

It is weird, because at first I tried running the first 2 keys (key1, key2) and it worked perfectly fine, then I copied and pasted down to create the next key (key3) and the error came up. When I try to debug, all the code above gets highlighted.

我想到的唯一其他方法是运行范围（A：K）获取的代码在工作表中选择，但我不想这样做。

The only other way I can think of is to run the code where the range (A:K) gets selected in the worksheet, but I don't want to do that.

推荐答案

看起来你正在尝试重做一些记录宏代码。记录的 Range.Find方法的记录代码非常详细。这里是你应该要的。

It looks like you are trying to rework some recorded macro code. The recorded code for a recorded Range.Find method is very verbose. Here is all that you should require.

With Sheets("Sheet1")
    With Intersect(.Range("A1").CurrentRegion, .Range("A:K"))
        .Cells.Sort Key1:=.Columns(6), Order1:=xlAscending, _
                    Key2:=.Columns(5), Order2:=xlAscending, _
                    Key3:=.Columns(4), Order3:=xlAscending, _
                Orientation:=xlTopToBottom, Header:=xlYes
    End With
End With

TBH，我不知道你是否可以添加 Ordercustom： = 1 到它的每一行。 IIRC，一次不能在一个字段上执行自定义排序。在这种情况下，执行三种，每个都有一个 Ordercustom：= 1 ，但请记住以相反的顺序执行;例如列D先，然后E，最后在F列上进行主排序。

TBH, I do not know if you can simply add Ordercustom:=1 to each line of that. IIRC, custom sorts cannot be performed on more than a single field at a time. In that case, perform three sorts, each with a Ordercustom:=1 but remember to do it in reverse order; e.g. column D first, then E and finally the primary sort on column F.

请注意，最多可以设置三列，用于设置主排序和二级排序这个方法。如果您需要更多，请先排序，然后在最后三个普遍的列上运行后续排序。

Please note that there are a maximum of three columns to set the primary sort and secondary sorts to with this method. If you require more, sort on those first and run a subsequent sort on the last three prevalent columns.

这篇关于在VBA中排序多个键;运行时错误450：参数数量错误或属性分配无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！