VBA number formatting?

Question

I am writing a macro package to format numbers in accounting format so that I don't have to click "alt - h - fm" every time I want to make a number accounting format (which my job requires). I have the code up to a certain point, but it is not matching excel's accounting format. Right now the $ appears one space before the number, rather than at the left edge of the cell (like it does for excel's accounting format). My present code formats to thousands with no decimals, all that is wrong is that the dollar sign doesn't appear at the left edge of the cell. Here is my code for the number format: Selection.NumberFormat = "_(* $ #,##0_);_(* $ (#,##0);_(* ""$ -""?_);_(@_)" How should I change it so that the dollar sign appears at the beginning of the cell? Thank you for the help! this has been stumping me for a while now...

Answer 1

((Late answer from 12 July. When I followed my own advice, I got a different code.

Selection.NumberFormat = “_($* #,##0.00_);_($* (#,##0.00);_($* “”-“”??_);_(@_)”

Try that and see how it works.))

One way to find out is to have Excel show the code it would use to make the manual reformatting.

To be concrete, perform the following:

1) Start Macro

2) select a cell that is not yet in accounting form

3) hit alt-h-fm to change the cell to accounting form

4) End Macro

Now read the code that Excel generated for your action.

Answer 2

Try this:

_($* #,##0_);_($* (#,##0);_(* ""$ -""?_);_(@_)

I moved the $ sign before the *

Hope this helps.