vba - Excel VBA错误处理程序不适用于 'run-time error 13: type mismatch'

Question

我正在为包含大约10个工作表的Excel文件运行下面的子程序，每个工作表均包含链接到同一数据源的数据透视表。我在数据中有两个版本的“市场”和“区域”字段(即“市场(SC)”，“市场(AN)”，“区域(SC)”，“区域(AN)”)，并且需要以便能够在它们之间轻松切换。我设置了代码，首先将对应的 slice 器置于最前面(它们叠加在一起，以便隐藏其他 slice 器)，然后遍历每个数据透视表并交换其他“市场”和“区域”字段(保持相同的位置，等等) )。

由于我使用属性“.SourceName”来标识字段，因此将“Values” PivotField与我的字符串进行比较时，循环会出错。我输入了“On Error Goto next_fld”，告诉它在发生这种情况时跳至下一个字段，但这仅适用于10个工作表中的8个-对于其他两个工作表，我会收到错误“运行时错误” 13'Type Mismatch”，并且调试屏幕突出显示“***”行。如果我使用“On Error Resume Next”，则假定If语句为True，并执行许多不需要的操作(合并各种数据透视表)。

我是自学成才的，并且对错误处理程序没有完全的了解，但是从我发现的用于修复此错误的资源中，该处理程序应该注意这一点(它确实适用于8/10工作表) )。

这是我的代码:

Sub SwapMktRegFields()

Dim ws As Worksheet, shp As Shape
Dim i As Integer
Dim target As String, repl As String

target = Sheet5.Range("E3").value

 'Identify current field, use other as repl(acement)
Select Case target
     'AN slicers selected
    Case Is = "AN"
        target = "(AN)"
        repl = "(SC)"
        Sheet5.Range("E3").value = "SC"
     'SC slicers selected
    Case Is = "SC"
        target = "(SC)"
        repl = "(AN)"
        Sheet5.Range("E3").value = "AN"
End Select

 'Bring replacement slicers to front (some are in shape groups)
For Each ws In ThisWorkbook.Worksheets
    For Each shp In ws.Shapes
        Select Case shp.Type
            Case Is = msoGroup
                For i = 1 To shp.GroupItems.Count
                    If shp.GroupItems(i).Name Like "Market " & target & "*" Or shp.GroupItems(i).Name Like "Region " & target & "*" Then shp.GroupItems(i).ZOrder msoSendToBack
                Next i
            Case Else
                If shp.Name Like "Market " & target & "*" Or shp.Name Like "Region " & target & "*" Then shp.ZOrder msoSendToBack
        End Select
    Next shp
Next ws



 'Replace old PivotFields with replacement PivotFields

Dim pvt As PivotTable
Dim fld As PivotField
Dim orient As Long, pos As Long

' MY ERROR HANDLER
On Error GoTo next_fld
For Each ws In ThisWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        For Each fld In pvt.PivotFields
 ' *** ERROR ON NEXT LINE WHEN fld IS 'VALUES'
            If fld.SourceName = "Market " & target And fld.Orientation <> xlHidden Then
                orient = fld.Orientation
                pos = fld.Position
                fld.Orientation = xlHidden
                With pvt.PivotFields("Market " & repl)
                    .Orientation = orient
                    .Position = pos
                End With
            ElseIf fld.SourceName = "Region " & target And fld.Orientation <> xlHidden Then
                orient = fld.Orientation
                pos = fld.Position
                fld.Orientation = xlHidden
                With pvt.PivotFields("Region " & repl)
                    .Orientation = orient
                    .Position = pos
                End With
            End If
next_fld:
        Next fld
    Next pvt
Next ws

 'A custom function to clear filters and re-apply a default
ResetPivotFilters

End Sub

最奇怪的部分是，该错误与使用此代码的其他8张纸完全相同。如果我完全删除错误处理程序，我将在其他工作表上看到完全相同的 pop 窗口和突出显示的行...任何建议将不胜感激!谢谢

Answer 1

蒂姆，这非常有帮助，并回答了我的问题。我将代码结尾更新为以下内容:

ResetPivotFilters

Exit Sub

err_handler:
Resume next_fld

End Sub

并将我的错误处理启用行更新为“On Error Goto err_handler”。现在工作。谢谢!