使用VBA添加计算字段 [英] Adding calculated field using VBA

问题描述

am尝试将计算所得的字段添加到在VBA中创建的数据透视表中(Excel 2010).数据透视表正在运行，并且一切都出现了-除了计算字段完全不存在之外.

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.

我正在使用的代码如下:

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

谢谢

推荐答案

查看代码，您尚未将计算字段作为数据字段添加到数据透视表中.

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

这篇关于使用VBA添加计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！