VBA NumberFormat problem

  • Thread starter Thread starter Jac Tremblay
  • Start date Start date
J

Jac Tremblay

Hi,
I use Excel 2000 french version with Windows XP Pro.
When I use this:
Selection.NumberFormat = _
"* # ##0.00\ ;[Red] * (# ##0.00);""- """
The numbers > 999 999 do not display the space (separator for the thousands
in French).
When I use this:
Selection.NumberFormat = _
"* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
The numbers < 1 000 display a space before the opening parenthese (which is
not acceptable).
Another thing: I would like the numbers > 999 999 999,99 to display as "###
### ### ##0,00" but how many # should one use to satisfy Excel (for negative
number as well)?
Note: in French, the decimal point is a comma.
Thanks.
 
Try this:

Selection.NumberFormatLocal = _
"* # ##0,00\ ;[Red] * (# ##0,00);""- """


--
Jim
| Hi,
| I use Excel 2000 french version with Windows XP Pro.
| When I use this:
| Selection.NumberFormat = _
| "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| The numbers > 999 999 do not display the space (separator for the
thousands
| in French).
| When I use this:
| Selection.NumberFormat = _
| "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| The numbers < 1 000 display a space before the opening parenthese (which
is
| not acceptable).
| Another thing: I would like the numbers > 999 999 999,99 to display as
"###
| ### ### ##0,00" but how many # should one use to satisfy Excel (for
negative
| number as well)?
| Note: in French, the decimal point is a comma.
| Thanks.
| --
| Jac Tremblay
 
Jim this gives me results such as 1,2 34,567
There is a space between 2 and 3 but commas are still used for thousands
separators
There are no decimal places
best wishes
 
This worked great for me with French settings of course. So strange it
wouldn't for you.<g>

--
Jim
| Jim this gives me results such as 1,2 34,567
| There is a space between 2 and 3 but commas are still used for thousands
| separators
| There are no decimal places
| best wishes
| --
| Bernard
| | > Try this:
| >
| > Selection.NumberFormatLocal = _
| > "* # ##0,00\ ;[Red] * (# ##0,00);""- """
| >
| >
| > --
| > Jim
| > | > | Hi,
| > | I use Excel 2000 french version with Windows XP Pro.
| > | When I use this:
| > | Selection.NumberFormat = _
| > | "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| > | The numbers > 999 999 do not display the space (separator for the
| > thousands
| > | in French).
| > | When I use this:
| > | Selection.NumberFormat = _
| > | "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| > | The numbers < 1 000 display a space before the opening parenthese
(which
| > is
| > | not acceptable).
| > | Another thing: I would like the numbers > 999 999 999,99 to display as
| > "###
| > | ### ### ##0,00" but how many # should one use to satisfy Excel (for
| > negative
| > | number as well)?
| > | Note: in French, the decimal point is a comma.
| > | Thanks.
| > | --
| > | Jac Tremblay
| >
|
|
 
Hi Jim,
It does work perfectly. I guess the NumberFormatLocal uses the local
settings from the control panel.
I am now at home with Excel 2007 English with Windows XP Pro French. I will
try it tomorrow at the office where Excel 2000 is still in the portrait.
Thank you for your reply. It makes my day.
 
Hi Bernard,
Try to change your local settings in the control panel. Make sure you have
this for the number format:
123 456 789,00
Wish you the best.
--
Jac Tremblay


Bernard Liengme said:
Jim this gives me results such as 1,2 34,567
There is a space between 2 and 3 but commas are still used for thousands
separators
There are no decimal places
best wishes
--
Bernard
Jim Rech said:
Try this:

Selection.NumberFormatLocal = _
"* # ##0,00\ ;[Red] * (# ##0,00);""- """


--
Jim
| Hi,
| I use Excel 2000 french version with Windows XP Pro.
| When I use this:
| Selection.NumberFormat = _
| "* # ##0.00\ ;[Red] * (# ##0.00);""- """
| The numbers > 999 999 do not display the space (separator for the
thousands
| in French).
| When I use this:
| Selection.NumberFormat = _
| "* # ### ##0.00\ ;[Red] * (# ### ##0.00);""- """
| The numbers < 1 000 display a space before the opening parenthese (which
is
| not acceptable).
| Another thing: I would like the numbers > 999 999 999,99 to display as
"###
| ### ### ##0,00" but how many # should one use to satisfy Excel (for
negative
| number as well)?
| Note: in French, the decimal point is a comma.
| Thanks.
| --
| Jac Tremblay
Click to expand...
Click to expand...
 
Back
Top