数据透视表格式设置返回错误

Question

我正在处理一个宏，在这个宏中我正在把PivotTables放在一起。

我的问题:我不能让计算部分工作。每次在PivotField(18)应该计算.Function=xlCount的阶段运行代码时，我都会得到一个错误。我得到的错误是"1004选择range类的方法失败“。

我在网上和MS帮助部分找到的所有示例都是按照我设置代码的方式构造的。我错过了什么？

任何帮助都是非常感谢的。

我一天中大部分时间都在做这件事。下面的页面(Link)非常有帮助，但并没有帮助我解决这个特殊的问题。

' Start of PivotSpecific Formatting
Set pt = ActiveSheet.PivotTables("PT" & wsNewWorksheetName)
With pt
    ' Prevent AutoFormatting of Columns
    .HasAutoFormat = False
    ' Hide Field Headers
    .DisplayFieldCaptions = False
    .TableStyle2 = "PivotStyleMedium2"
    ' Apply calculation to numbers and change Caption
    With .PivotFields(18)
        .Caption = "Demand"
        .Function = xlCount
        .NumberFormat = "#,##0"
    End With
    With .PivotFields(15)
        .Caption = "Lowest OP"
        .Function = xlMin
        .NumberFormat = "#,##0.00"
    End With
End With

更新1:透视表的屏幕截图

​

更新2:来自bonCodigo的测试代码这里是根据我的工作簿进行调整的代码。这样，我就得到了运行时错误91 : Set pvField = pvTable.PivotFields(18)

Dim wkSheet As Worksheet
Dim pvTable As PivotTable
Dim pvField As PivotField

Set wkSheet = ThisWorkbook.Worksheets(wsNewWorksheetName)
Set pvTble = wkSheet.PivotTables("PT" & wsNewWorksheetName)
Set pvField = pvTable.PivotFields(18)

With pvField
     .Caption = "Demand"
     .Function = xlCount
     .orientation = xlDataField
     .NumberFormat = "#,##0"
End With

Set pvField = Nothing
Set pvTable = Nothing
Set wkSheet = Nothing

Answer 1

请尝试一下，并对您的结果进行评论。所以我们可以从那里看到。

Option Explicit

'-- whatever other code you may have... 

Dim wkSheet as Worksheet
Dim pvTable as PivotTable
Dim pvField as PivotField

Set wkSheet = ThisWorkbook.Worksheets("Sheet1") '-- user proper sheet name explicitly
Set pvTble = wkSheet.PivotTables("PivotTable1") 
   ' or wkSheet.PivotTables(1) '-- use pivot table name or index
Set pvField = pvTable.PivotFields("SomeField") '-- use pivot field name or index

' -- do whatever you want to do with the pivot table

' -- do what you need to do with the pivot field
With pvField
     .Caption = "Demand"
     .Orientation = xlDataField '--<< THIS could well be the culprit
     .Function = xlCount
     .NumberFormat = "#,##0"
End With

Set pvField = Nothing
Set PvTable = Nothing
Set wkSheet = Nothing

由于您有多个字段要格式化，因此我建议您在使用DataFields Index时连续使用循环

例如pvTable.DataFields(1).Function = xlCount

否则，您将不得不单独定义它们。最好总是将变量指定为要交互的对象的引用。:)