Calculated Field number format in VBA

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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!
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top