Excel VBA错误处理程序无法处理“运行时错误13:类型不匹配”

I am running the sub below for an Excel file containing about 10 worksheets, each containing a pivottable linked to the same data source. I have two versions of the 'Market' and 'Region' fields in my data (i.e. 'Market (SC)', 'Market (AN)', 'Region (SC)', 'Region (AN)'), and need to be able to switch between them easily. I set up the code to first bring the corresponding slicers to the front (they are superimposed so that will hide the other), then loop through each pivottable and swap the other 'Market' and 'Region' fields (maintaining the same position, etc.).

我正在为一个包含大约10个工作表的Excel文件运行下面的子文件，每个工作表都包含一个连接到相同数据源的数据透视表。我的数据中有两个版本的“市场”和“区域”字段。“市场(SC)”、“市场(AN)”、“区域(SC)”、“区域(AN)”)，并且需要能够在它们之间轻松切换。我设置代码首先将相应的切片器带到前面(它们是重叠的，这样可以隐藏另一个)，然后循环遍历每个数据透视表，并交换另一个“市场”和“区域”字段(保持相同的位置，等等)。

Since I'm using the property ".SourceName" to identify the field, the loop runs into an error when the "Values" PivotField is compared to my string. I've put in "On Error Goto next_fld" to tell it to skip to the next field when this occurs, but this only works for 8 of the 10 worksheets -- for the other two I get the error "Run-time error '13' Type Mismatch" and the debug screen highlights the " *** " line. If I use "On Error Resume Next", it assumes that the If statement was True and carries out a lot of unwanted actions (messes up various PivotTables).

因为我在使用属性"要识别字段，当将“值”数据透视表字段与我的字符串进行比较时，循环就会出现错误。我放在“Error Goto next_fld”告诉它跳到下一个字段当这种情况发生时,但这只适用于8的10个工作表,其他两种我得到错误的“运行时错误‘13’类型不匹配”和调试屏幕凸显了“* * *”。如果我使用“On Error Resume Next”，它假定If语句为真，并执行许多不需要的操作(使各种数据透视表混乱)。

I'm self-taught and do not have a complete understanding of the error handler, but from the resources I've come across to fix this error, the handler should be taking care of this (which it does work for 8/10 worksheets).

我是自学的，对错误处理程序没有完全的理解，但是从我遇到的修复这个错误的资源中，处理程序应该负责这个问题(它对8/10工作表是有效的)。

Here is my code:

这是我的代码:

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

The weirdest part is that the error is EXACTLY THE SAME as the other 8 sheets that work with this code. If I remove the error handler completely, I get the exact same pop-up and line highlighted for the other sheets... Any suggestions would be greatly appreciated! Thanks

最奇怪的是，错误与使用此代码的其他8个表完全相同。如果我完全删除错误处理程序，我将得到与其他表相同的弹出框和高亮显示的行……如有任何建议，我们将不胜感激!谢谢

1 个解决方案

#1

Tim, this was very helpful and answered my question. I updated the end of my code to the following:

蒂姆，这很有用，回答了我的问题。我将我的代码的结尾更新为:

ResetPivotFilters

Exit Sub

err_handler:
Resume next_fld

End Sub

and updated my error handling enabling line to "On Error Goto err_handler". Working now. Thank you!

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

#1