Format calculated item in vba sub

yodayoda

New Member
Joined
Apr 29, 2003
Messages
3
I have an excel vba script that builds a pivot table. In it I create three calculated items. The first is defined as "a-b", the second is "b-c". They get formatted as dollars according to the following: PT.PivotFields("Sum of Amount").NumberFormat = "$#,##0_);[Red]($#,##0)". I have now added a third one that is "a/b" but can't figure out how to format it as PT.PivotFields("Sum of Amount").NumberFormat = "%;[Red](%)" while still keeping the first two as dollars. Boiled down, I'm trying to get the first two calculated items looking like dollars and the third looking like percentage. Thanks for any guidance in this.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Hi:
I am not an expert in Pivot Table scripts but I suspect you could record a macro where you change the field formatting and look at that macro to see what the relevant VBA properties and methods are - then edit as needed.

Regards.
 

yodayoda

New Member
Joined
Apr 29, 2003
Messages
3
I tried that and it just tells me what the format should look like. Here's the section of code where I'm hung up.

PT.PivotFields("Version").CalculatedItems.Add "InvStrat-BudBuild", "= InvStrat- BudBuild"

PT.PivotFields("Version").CalculatedItems.Add "OrigPlan-BudBuild", "= OrigPlan-BudBuild"

PT.PivotFields("Version").CalculatedItems.Add "Bud/LRP", "= BudBuild/InvStrat"

PT.PivotFields("Sum of Amount").NumberFormat = "$#,##0_);[Red]($#,##0)"

All three calculated items are manipulating the data field "Sum of Amount". So the two instances get formatted as dollars using the last statement above. Since the third item is also "Sum of Amount" data, if I rewrite the last statement to be PT.PivotFields("Sum of Amount").NumberFormat = "0%;[Red](0%)", then the first two items also become percentages. Thus my dilemma - how to get the first two as $$ and the third one as %. Thanks.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Ahh...good old calculated Items.

I am not sure if this is possible if these items are all in the same column (sum of amount). You might need to bit the bullet and format as "#,###.00" - so percents are decimals less than one.

You can try getting this into a table format rather than a report format...that could give you more options for formatting columns of data. This may not be possible in your situation.

Another solution is to custom format the cells, not the pivot fields -- see below.

It might also be possible to have two sum of amount columns to work on...again, maybe this is not feasible in your situation.

Sometimes Pivot Table are good for the crunching and the final report format for presentation is outside the Pivot Table, or on another sheet.

Basically, as far as pivot tables go, if you can do it in Excel you can do it in Excel VBA. So if you can get this to work in a native interface, you could macro the same steps. There won't be any special properties or methods you can use in VBA to do what you can't do in native Excel.

Sample sub:
Format cells (using regular cell properties, not pivot table properties. I've been lazy with the formats as anything not a number or a percent is hidden...you might need to clean this up. Who knows what it would do to text. And, of course, anything that looks like a percent will be formatted as one. Also, sometimes to select pivot table cells you need to "navigate into" the table with arrow keys and the shift key.

Code:
Sub myFormats()
Dim c As Range
For Each c In Selection
    If c.Value < 1 And c.Value > 0 Then
        c.NumberFormat = "0.00%;;"
    Else: c.NumberFormat = "$#,##0_);[Red]($#,##0);"
    End If
Next c
End Sub
 

Forum statistics

Threads
1,181,056
Messages
5,927,865
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top