Hi,

I have a number formatting problem. When I type in this custom format,
"N"__#,##0.00;[Red]"N"__(#,##0.00);0.00
the results are exactly what I want: N 88.00 for positive and N (88.00)
for negative.
But I don't know how to write the code for the above.

The closest I can get is:
Range("OtherCurrencyCells").NumberFormat = Range("CurrencySymbol").Value &
"__#,##0.00;__[red](#,##0.00);0.00"
which results in this in the Custom Format window:
"N"__#,##0.00;[Red]__(#,##0.00);0.00
The above won't preface the negative number with the N.

If anyone can help me figure out how to get that second currency sign, i.e.
"N", into the negative number position, I would surely appreciate it.

The "N" can vary, depending on what country the user is from that is using
the spreadsheet, that's why I can't just enter it as a fixed Custom format.

Many thanks,
Harold

too simple

Range("A1").NumberFormat = """N""__#,##0.00;[Red]""N""__(#,##0.00);0.00"

the numberformat need to be a string with double quotes at beginning and
end. Since the string contains double quotes, You need to put two sets of
double quotes inside a string for double quotes to appear in the results.

"Harold Good" wrote:

Hi,

I have a number formatting problem. When I type in this custom format,
"N"__#,##0.00;[Red]"N"__(#,##0.00);0.00
the results are exactly what I want: N 88.00 for positive and N (88.00)
for negative.
But I don't know how to write the code for the above.

The closest I can get is:
Range("OtherCurrencyCells").NumberFormat = Range("CurrencySymbol").Value &
"__#,##0.00;__[red](#,##0.00);0.00"
which results in this in the Custom Format window:
"N"__#,##0.00;[Red]__(#,##0.00);0.00
The above won't preface the negative number with the N.

If anyone can help me figure out how to get that second currency sign, i.e.
"N", into the negative number position, I would surely appreciate it.

The "N" can vary, depending on what country the user is from that is using
the spreadsheet, that's why I can't just enter it as a fixed Custom format.

Many thanks,
Harold

Use a String variable for your symbol and then add it into your Format like
this:

Dim symb As String
symb = Range("CurrencySymbol").Value
Range("OtherCurrencyCells").NumberFormat = _
symb & "__#,##0.00;__[red]" & symb & "(#,##0.00);0.00"

Mike F
"Harold Good" wrote in message
...
Hi,

I have a number formatting problem. When I type in this custom format,
"N"__#,##0.00;[Red]"N"__(#,##0.00);0.00
the results are exactly what I want: N 88.00 for positive and N (88.00)
for negative.
But I don't know how to write the code for the above.

The closest I can get is:
Range("OtherCurrencyCells").NumberFormat = Range("CurrencySymbol").Value &
"__#,##0.00;__[red](#,##0.00);0.00"
which results in this in the Custom Format window:
"N"__#,##0.00;[Red]__(#,##0.00);0.00
The above won't preface the negative number with the N.

If anyone can help me figure out how to get that second currency sign,
i.e. "N", into the negative number position, I would surely appreciate it.

The "N" can vary, depending on what country the user is from that is using
the spreadsheet, that's why I can't just enter it as a fixed Custom
format.

Many thanks,
Harold

Thanks Mike, that worked perfectly.

Harold


"Mike Fogleman" wrote in message
. ..
Use a String variable for your symbol and then add it into your Format
like this:

Dim symb As String
symb = Range("CurrencySymbol").Value
Range("OtherCurrencyCells").NumberFormat = _
symb & "__#,##0.00;__[red]" & symb & "(#,##0.00);0.00"

Mike F
"Harold Good" wrote in message
...
Hi,

I have a number formatting problem. When I type in this custom format,
"N"__#,##0.00;[Red]"N"__(#,##0.00);0.00
the results are exactly what I want: N 88.00 for positive and N (88.00)
for negative.
But I don't know how to write the code for the above.

The closest I can get is:
Range("OtherCurrencyCells").NumberFormat = Range("CurrencySymbol").Value
& "__#,##0.00;__[red](#,##0.00);0.00"
which results in this in the Custom Format window:
"N"__#,##0.00;[Red]__(#,##0.00);0.00
The above won't preface the negative number with the N.

If anyone can help me figure out how to get that second currency sign,
i.e. "N", into the negative number position, I would surely appreciate
it.

The "N" can vary, depending on what country the user is from that is
using the spreadsheet, that's why I can't just enter it as a fixed Custom
format.

Many thanks,
Harold

Thanks Joel, this solution works as well. I didn't know that about the
double quotes.

Harold


"Joel" wrote in message
...
too simple

Range("A1").NumberFormat = """N""__#,##0.00;[Red]""N""__(#,##0.00);0.00"

the numberformat need to be a string with double quotes at beginning and
end. Since the string contains double quotes, You need to put two sets of
double quotes inside a string for double quotes to appear in the results.

"Harold Good" wrote:

Hi,

I have a number formatting problem. When I type in this custom format,
"N"__#,##0.00;[Red]"N"__(#,##0.00);0.00
the results are exactly what I want: N 88.00 for positive and N
(88.00)
for negative.
But I don't know how to write the code for the above.

The closest I can get is:
Range("OtherCurrencyCells").NumberFormat = Range("CurrencySymbol").Value
&
"__#,##0.00;__[red](#,##0.00);0.00"
which results in this in the Custom Format window:
"N"__#,##0.00;[Red]__(#,##0.00);0.00
The above won't preface the negative number with the N.

If anyone can help me figure out how to get that second currency sign,
i.e.
"N", into the negative number position, I would surely appreciate it.

The "N" can vary, depending on what country the user is from that is
using
the spreadsheet, that's why I can't just enter it as a fixed Custom
format.

Many thanks,
Harold