VBA - Calculated Fields in Pivot Table

[Catmao]

Hi All,

Here is my source data (6 columns):
Name Gender NewGrade1(NewField1) NewGrade2(NewField2) OldGrade1(OldField1)
OldGrade2(OldField2)
I want to create a pivot table and calculated fields of % Change to Field1, % Change to Field2, and the filed names are "Grade1 % Change", "Grade2 % Change".
There could be more fields, so I want use For/Next function.

Below is my code. The red is where I got error 1004: Unable to get the PivotFields property of the PivotTable class. (Field(i) is Grade(i), e.g. Field1 is Grade1)
For i = 1 To NumFields
PvtTbl.CalculatedFields.Add Name:="Field(i)Change", Formula:="=IF(OldField(i)=0,0,(NewField(i) -OldField(i))/OldField(i))"
With PvtTbl.PivotFields("Field(i)Change")
.Orientation = xlDataField
.Function = xlSum
.Position = i
.NumberFormat = "0.00%"
.Caption = Field(i) & " % Change"
End With
Next i

Thanks in advance!

 

[Worf]

Hi
See if this example is useful:

Sub AddCalcField()
Dim pvtT As PivotTable, i%, si$, fml$
Set pvtT = ActiveSheet.PivotTables(1)
i = 1
si = CStr(i)
fml = "=IF(OldField" & si & "=0,0,(NewField" & si & "-OldField" & si & ")/OldField" & si & ")"


pvtT.CalculatedFields.Add "Field" & si & "Change", fml, True


With pvtT.PivotFields("Field" & si & "Change")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = i
    .NumberFormat = "0.00%"
    .Caption = "Field" & i & " % Change Final"
End With
End Sub