使用 VBA 添加计算字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26969830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding calculated field using VBA
提问by Marc L
am trying to add a calculated field to a pivot table created in VBA (Excel 2010). The pivot table is working and everything is appearing- except for the calculated field which is completely absent.
我正在尝试将计算字段添加到在 VBA (Excel 2010) 中创建的数据透视表。数据透视表正在工作，一切都在出现 - 除了完全不存在的计算字段。
The code I am using is as follows:
我使用的代码如下：
Sub Create_Pivot_Table_for_chart2()
Dim wsnew As Worksheet
Dim objPivotcache As PivotCache
Dim objPivotTable As PivotTable
'Adding new worksheet
Set wsnew = Worksheets.Add
wsnew.Name = "Test5"
'Creating Pivot cache
Set objPivotcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, "'datasheet'!B1:BX1000")
'Creating Pivot table
Set objPivotTable = objPivotcache.CreatePivotTable(wsnew.Range("A1"))
'Setting Fields
With objPivotTable
'set row field
With .PivotFields("Prosperator")
.Orientation = xlRowField
.Position = 1
End With
'set column field
With .PivotFields("Business Name")
.Orientation = xlRowField
.Position = 2
End With
'set calculated field
.CalculatedFields.Add "TOGrowth%", "= ('ITD Average'- 'Pre-ignition T/O')/'Pre-ignition T/O'"
'set data field
.AddDataField .PivotFields("Pre-ignition T/O"), "PI T/O", xlSum
.AddDataField .PivotFields("ITD Average"), "ITD", xlSum
End With
END SUB
Thanks
谢谢
采纳答案by Gareth
Looking at your code, you haven't added the calculated field to the pivot table as a data field.
查看您的代码，您还没有将计算字段作为数据字段添加到数据透视表中。
You need to add the following line of code after you've created the field:
创建字段后，您需要添加以下代码行：
.PivotFields("TOGrowth%").Orientation = xlRowField
