Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run a VB code when you click on the data field of a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Tables - standard Data Field function | Excel Discussion (Misc queries) | |||
Excel 2007 Pivot Table - Group Field function is not always availa | Excel Worksheet Functions | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Pivot table field swapping in code | Excel Programming |