Pivot Field Function Code
 

hey guys

Below is a the code I use to calculate (summarize) a
particular pivot field in my pivot table:

With ActiveSheet.PivotTables("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Percent Overdue"
.Position = 1
.Function = xlAverage
End With

As you can see the .Function tells the field how to
calculate.

Is it possible to put a custom formula in there such as:
..Function = (Total28- 'On Time28')/ Total28


When I do this VBA highlights it in red. Is there a way
to do this?



Thank you
Todd Huttenstine

Pivot Field Function Code
 

Hi Todd
AFAIK this is not possible (you can't use custom function if you use
the wizard)

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
hey guys

Below is a the code I use to calculate (summarize) a
particular pivot field in my pivot table:

With ActiveSheet.PivotTables("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Percent Overdue"
.Position = 1
.Function = xlAverage
End With

As you can see the .Function tells the field how to
calculate.

Is it possible to put a custom formula in there such as:
.Function = (Total28- 'On Time28')/ Total28


When I do this VBA highlights it in red. Is there a way
to do this?



Thank you
Todd Huttenstine

Pivot Field Function Code
 

What happens if I dont use the wizard?

Can I specifiy in the code I have the special formula I
want to use?

I need it to use the formula:
(Total28- 'On Time28')/ Total28

-----Original Message-----
Hi Todd
AFAIK this is not possible (you can't use custom function
if you use
the wizard)

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
hey guys

Below is a the code I use to calculate (summarize) a
particular pivot field in my pivot table:

With ActiveSheet.PivotTables
("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Percent Overdue"
.Position = 1
.Function = xlAverage
End With

As you can see the .Function tells the field how to
calculate.

Is it possible to put a custom formula in there such as:
.Function = (Total28- 'On Time28')/ Total28


When I do this VBA highlights it in red. Is there a way
to do this?



Thank you
Todd Huttenstine

.

Pivot Field Function Code
 

Hi
if nearly all cases VBA does NOT provide more features than the GUI. So
in this case as you can't do this manually also VBA does not support
this feature (using custom formulas in the pivot table). One workaround
could be to use a helper column in your source data in include this
helper column in your pivot table as data item (depend on the type of
your formula)

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
What happens if I dont use the wizard?

Can I specifiy in the code I have the special formula I
want to use?

I need it to use the formula:
(Total28- 'On Time28')/ Total28

-----Original Message-----
Hi Todd
AFAIK this is not possible (you can't use custom function if you use
the wizard)

--
Regards
Frank Kabel
Frankfurt, Germany


Todd Huttenstine wrote:
hey guys

Below is a the code I use to calculate (summarize) a
particular pivot field in my pivot table:

With ActiveSheet.PivotTables ("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Percent Overdue"
.Position = 1
.Function = xlAverage
End With

As you can see the .Function tells the field how to
calculate.

Is it possible to put a custom formula in there such as:
.Function = (Total28- 'On Time28')/ Total28


When I do this VBA highlights it in red. Is there a way
to do this?



Thank you
Todd Huttenstine

.