Calculated Field number format in VBA

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,748
Office Version
  1. 365
Platform
  1. Windows
Hi!

How do you format PivotTable Calculated Field numbers in VBA? I'm trying to format the numbers so that all the actual numbers show with two decimals and plain zeroes show as "".

The
Code:
.NumberFormat = "# ###.##"
seems to work in all the normal data fields but with the Calculated Fields this shows zeroes as ",".

I'm using XL2003.

Thank you already in advance!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The formula is simply "=Weight/1000". The weight is in kilos in the orginal data rows but tons are much easier to manage.
 
Upvote 0
If you format the original field as "# ##0,00 ;-# ##0,00 ;" (no quotes) it will be scaled by 1000 and zeroes will be suppressed. You may need to change the decimal separator from a comma to a dot.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top