使用vba代码进行自定义排序

Question

对于下面的代码段，当它到达下面的代码段时，我得到了一个运行时错误'13'，类型不匹配错误

ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Add Key:= _
f, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal

上面的那段代码在下面的完整代码中，我将它放在粗体中，它接近代码的末尾。

我正在尝试做的是根据当前的State列进行过滤(这很好用)，然后我想让它按第二列和第三列进行自定义排序("PCR No.“和“Accn.ID”)。如果我只是使用原始录制的代码(Range("B2:B2000")，SortOn:=xlSortOnValues，Order:=xlAscending，DataOption:=)，它将工作得很好，但问题是，如果我决定在开始时使用列，那么宏不会中断，所以我试图让它按列名而不是列号进行自定义排序。

在这里，任何帮助都将不胜感激。

Sub CommercialView() ' ' CommercialView Macro '

  ' Dim wrkbk, sourceBk As Workbook Set sourceBk = Application.ActiveWorkbook 'Clear Filter for all Columns START With ActiveSheet If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If Else If .FilterMode Then .ShowAllData End If End If End With 'Clear Filter from all Columns END

  'Copy the required columns and add them to the destination spreadsheet START
  Workbooks.Add
  Set wrkbk = Application.ActiveWorkbook
  sourceBk.Activate
  wrkbk.Activate
  sourceBk.Activate

  Dim aCell1, aCell2, aCell3, aCell4, aCell5, aCell6, aCell7, aCell8, aCell9, aCell10, aCell11, aCell12 As Range
  Dim strSearch1, strSearch2, strSearch3, strSearch4, strSearch5, strSearch6, strSearch7, strSearch8, strSearch9, strSearch10, strSearch11, strSearch12 As String

  strSearch1 = "Change Request Description"
  strSearch2 = "PCR No."
  strSearch3 = "Accn. ID"
  strSearch4 = "Current State"
  strSearch5 = "Approved Date"
  strSearch6 = "Project"
  strSearch7 = "Planned Commencement Date"
  strSearch8 = "Notes"
  strSearch9 = "Total Price (IIA, DIA, Execution ($)"
  strSearch10 = "Price Calculator Status"
  strSearch11 = "OM Entry"
  strSearch12 = "CVP Ref. No."

  Set aCell1 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch1, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell2 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch2, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell3 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch3, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell4 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch4, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell5 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch5, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell6 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch6, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell7 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch7, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell8 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch8, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell9 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch9, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell10 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch10, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell11 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch11, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  Set aCell12 = Sheets("3. PMO Internal View").Rows(1).Find(What:=strSearch12, LookIn:=xlValues, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False)

  '~~> Do the copying here

  Sheets("3. PMO Internal View").Range(Sheets("3. PMO Internal View").Columns(aCell1.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell2.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell3.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell4.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell5.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell6.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell7.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell8.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell9.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell10.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell11.Column).Address & "," _
        & Sheets("3. PMO Internal View").Columns(aCell12.Column).Address).Copy

  'Range("A1,B1,C1,D1,E1,G1,H1,I1,R1,V1,W1,X1").EntireColumn.Select
  'Selection.Copy

  Range("A2").Select
  wrkbk.Activate
  ActiveSheet.Paste
  Selection.AutoFilter
  'Copy the required columns and add them to the destination spreadsheet END

  'To remove data validation START
  Cells.Select
  With Selection.Validation
      .Delete
      .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
      :=xlBetween
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
  End With
  'To remove data validation END

  wrkbk.Activate
  wrkbk.Sheets("Sheet1").Select

  'Filter Column Price Calculator Status with those that Require Review from Pricing START

  Dim p As Integer, rngData As Range
  Set rngData = Range("A1").CurrentRegion
  p = Application.WorksheetFunction.Match("Price Calculator Status", Range("A1:AZ1"), 0)

  rngData.AutoFilter Field:=p, Criteria1:="=Completed - Requires Review from Pricing"

  'Filter Column Price Calculator Status with those that Require Review from Pricing END

  'Copy the Status Definitions tab to the new worksheet START

  sourceBk.Sheets("2. Status Definitions").Copy _
  after:=ActiveWorkbook.Sheets("Sheet1")

  'Copy the Status Definitions tab to the new worksheet END

  wrkbk.Sheets("Sheet1").Select
  Range("A5").Select

  'Save to Desktop Directory as DOD folder name - Compatible for any user who runs the macro START

  Dim uName As String: uName = Environ("Username")

  fpath1 = "C:\Users\" & uName & "\Desktop\DOD"
  fpath2 = "C:\Users\" & uName & "\Desktop\DOD\Change Status Request Report"
  fpath3 = "C:\Users\" & uName & "\Desktop\DOD\Change Status Request Report\Commercial View"

  If Dir(fpath1, vbDirectory) = vbNullString Then MkDir fpath1
  If Dir(fpath2, vbDirectory) = vbNullString Then MkDir fpath2
  If Dir(fpath3, vbDirectory) = vbNullString Then MkDir fpath3
  ActiveWorkbook.SaveAs (fpath3 & "\Internal Change Status Request Report - Commercial View - " & Format(Now, "yyyy-mm-dd"))
  ActiveWorkbook.Close

  'Save to Desktop Directory as DOD folder name - Compatible for any user who runs the macro END

  'Return back to Overall CR Tracker and filter out Approved and Cancelled CRs START

  Dim s, f, g As Integer, rngData2, rngData5, rngData6 As Range
  Set rngData2 = Range("A1").CurrentRegion
  s = Application.WorksheetFunction.Match("Current State", Range("A1:AZ1"), 0)

  rngData2.AutoFilter Field:=s, Criteria1:=Array( _
  "Detailed Impact Assessment", "Draft – Yet to be Tabled at CCCM", _
  "Initial Impact Assessment", "New", "On Hold", "Pending Approval - Execution", _
  "Pending Approval - IIA"), Operator:=xlFilterValues

  Set rngData5 = Range("B1").CurrentRegion
  f = Application.WorksheetFunction.Match("PCR No.", Range("A1:AZ1"), 0)
  Set rngData6 = Range("C1").CurrentRegion
  g = Application.WorksheetFunction.Match("Accn. ID", Range("A1:AZ1"), 0)

  ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Clear
  **ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Add Key:= _
  f, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal**
  ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Add Key:= _
  g, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  With ActiveWorkbook.Worksheets("3. PMO Internal View").Sort
    .SetRange Range("A1:X2000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With

  'Return back to Overall CR Tracker and filter out Approved and Cancelled CRs END
End Sub

Answer 1

Sort的键参数应具有范围或单元格地址。您的f被定义为f = Application.WorksheetFunction.Match()，它返回一个数字。

你应该有像Set f = Range("A1")或f = "A1"这样的东西。Excel将使用包含指定单元格的列。

编辑1：

而不是：

f = Application.WorksheetFunction.Match("PCR No.", Range("A1:AZ1"), 0)
ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Add Key:= f

您应该使用：

f = Application.WorksheetFunction.Match("PCR No.", Range("A1:AZ1"), 0)
ActiveWorkbook.Worksheets("3. PMO Internal View").Sort.SortFields.Add Key:= Cells(1, f)

在查看您的代码时，脑海中浮现出一些其他注释：

您的变量声明并不做您认为的事情：

Dim s, f, g As Integer, rngData2, rngData5, rngData6 As Range
'is equivalent to
Dim s As Variant, f As Variant, g As Integer, rngData2 As Variant, rngData5 As Variant, rngData6 As Range

'you should write
Dim s As Integer, f As Integer, g As Integer, rngData2 As Range, rngData5 As Range, rngData6 As Range

你粘贴了太多代码。当我看到它的时候，我只是浏览了一下，我很幸运地看到了问题所在。我通常会跳过这样的问题。您应该尝试编写一个尽可能短的函数来重现相同的问题。这对你的帮助有两个原因:更有可能有人读到它并给你一个解决方案，更有可能的是你在减少问题的过程中自己解决问题。我经常在这里开始写问题，但我不会把它们贴出来，因为只要思考如何写它，你就能理解它，让我理解它。

将对你有帮助的答案标记为答案。我注意到你从来不这样做，许多人喜欢帮助别人，以换取那个小小的令人满意的复选标记。如果你是那个不满足的人，人们就不会帮助你。

编辑2：

我认为这就是您需要的(这次我测试了它)：

  Dim f As Integer, g As Integer, Sh As Worksheet
  Set Sh = Sheets("3. PMO Internal View")
  f = WorksheetFunction.Match("PCR No.", Sh.Range("A1:AZ1"), 0)
  g = WorksheetFunction.Match("Accn. ID", Sh.Range("A1:AZ1"), 0)
  Sh.Range("A1:X2000").Sort Key1:=Sh.Cells(1, f), Order1:=xlAscending, Key2:=Sh.Cells(1, g), Order2:=xlAscending

请注意，我总是使用Sh表来指定Range和Cells属性引用的表。这使您可以使用此功能，而不管活动工作表是什么。使用Cells(...)或Range(...)但不指定工作表时，默认为活动工作表，并强制您在对要排序的工作表进行排序之前激活该工作表。