Calculated Field number format in VBA

[Misca]

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

.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!

 

[Andrew Poulsom]

Can you gives us the formula of the calculated field?

 

[Misca]

The formula is simply "=Weight/1000". The weight is in kilos in the orginal data rows but tons are much easier to manage.

 

[Andrew Poulsom]

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.