Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #DIV/0! in VBA in a calculated field in a pivot table
I have written a program in VBA that creates pivot tables. There is a
calculated field which creates a percentage based on a division between sums of two of the fields in the pivot table. The denominator occasionally shows up as a "0", causing a #DIV/0! to appear. I have set the null string to a blank, but since the range that creates the sum has a formula in it, the sum still shows up as a "0". How can I code this to remove the #DIV/0!? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #DIV/0! in VBA in a calculated field in a pivot table
handle it in the calculated field definition
if(divisor = 0,0,current formula) -- Regards, Tom Ogilvy "lewscannon" wrote in message ... I have written a program in VBA that creates pivot tables. There is a calculated field which creates a percentage based on a division between sums of two of the fields in the pivot table. The denominator occasionally shows up as a "0", causing a #DIV/0! to appear. I have set the null string to a blank, but since the range that creates the sum has a formula in it, the sum still shows up as a "0". How can I code this to remove the #DIV/0!? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #DIV/0! in VBA in a calculated field in a pivot table
Without and code I will have to be generic but this should be close...
With ActiveSheet.PivotTables("PivotTable1") .DisplayErrorString = True .ErrorString = "0" End With instead of #DIV/0!it will show 0 as the errorstring... HTH "lewscannon" wrote: I have written a program in VBA that creates pivot tables. There is a calculated field which creates a percentage based on a division between sums of two of the fields in the pivot table. The denominator occasionally shows up as a "0", causing a #DIV/0! to appear. I have set the null string to a blank, but since the range that creates the sum has a formula in it, the sum still shows up as a "0". How can I code this to remove the #DIV/0!? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a Calculated Field from the Pivot Table field list | Excel Discussion (Misc queries) | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Creating a Calculated Field in a Pivot Table for same field | Excel Discussion (Misc queries) | |||
Calculated Field and Calculated Item in Pivot Table | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) |