Number format in a Macro

  • Thread starter Thread starter Dewayne
  • Start date Start date
D

Dewayne

I have the following number format code in a macro which gives me what I need
except then the cell value is 0, I need it to indicate a "-" (hyphen) or just
remain blank.

Selection.NumberFormat = "#,##0.0_);(#,##0.0)"

Any help would be appreciated.
Thank you,
 
DeWayne,

If you add a third parameter, that is for zero values, so add ;"-" to what
you have if you want zero to display as a hyphen. Your formatting code would
then look like this:

Selection.NumberFormat = "#,##0.0_);(#,##0.0); "-""

I think you have an extra paren in your first parameter, but maybe that is
intentional?
 
Thank you for you help. It is greatly appreciated.
I want to end up values such as "1,123.4" for millions, "123.4" for hundreds
of 1000's, "12.3" for tens of 1000's, 1.23 for 1000"s, 0.1 for 100's and so
on. Based on that, which parameter should I eliminate.
Thanks again
 
You would keep the same formatting, but divide all the numbers in the range
by 1000. Formatting can not change the value, you have to divide to get a new
number if you want to have 1,234.5 instead of 1,234,500.
 
You are welcome. Please rate my post, if you think it helped you. I'm trying
to get a gold star! (still a very long way to go, though)
 
Back
Top