I want to pass a number format to a variable. The following code is no
working:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = [($"US" #,##0_);[Red]($#,##0)]

I am getting a type mismatch error. I think there is a problem wit
either a lack of quotes or brackes. Or my variable declaration is o
the wrong type.

Can anyone tell me what wrong here?

Thank-you

--
Message posted from http://www.ExcelForum.com

Ok the format was wrong is should be

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "$US #,##0_);[Red]($#,##0)"

However when I try to do this:

Range("IncomeStatement").NumberFormat = CurrencyFormat

I get an error stating:

"Unable to set number format property on Range class. "

What am I doing wrong

--
Message posted from http://www.ExcelForum.com

Try it like this:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "[($""US"" #,##0_);[Red]($#,##0)]"
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"ExcelMonkey " wrote in message
...
I want to pass a number format to a variable. The following code is not
working:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = [($"US" #,##0_);[Red]($#,##0)]

I am getting a type mismatch error. I think there is a problem with
either a lack of quotes or brackes. Or my variable declaration is of
the wrong type.

Can anyone tell me what wrong here?

Thank-you.


---
Message posted from http://www.ExcelForum.com/

I tried that Rob. My cell has the value "$100" in it. When applyin
the format I got the following in the cell:

US #,##0_);[Red($100)]

Somethings wrong here

--
Message posted from http://www.ExcelForum.com

I neglected to notice that your original number format was invalid. Me
bad. As keepitcool demonstrated, you need to escape the U and S characters
somehow because otherwise Excel tries to interpret them as formatting
tokens. You can either do it with backslashes as he demonstrated or you can
surround the US in double quotes as you were trying to do originally. For
example, this will work:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"
Sheet1.Range("A1").NumberFormat = CurrencyFormat
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"ExcelMonkey " wrote in message
...
I tried that Rob. My cell has the value "$100" in it. When applying
the format I got the following in the cell:

US #,##0_);[Red($100)]

Somethings wrong here.


---
Message posted from http://www.ExcelForum.com/

So something really odd is happening here. When I use the code that yo
both submitted "$""US"" #,##0_);[Red]($#,##0)", it will not work fo
the US dollar option. See below. I have 5 currencies options than ca
be picked from a cell called "CurrencyType". They all work except th
US dollar option. Also note that the CDN dollar option has the exac
same code as the US Dollar option but it works. Can't figure out wh
one would work while the other would not. When I use the US Dollar
option and I pass the cursor over CurrencyFormat is say
CurrencyFormat = "False". but it does not do this for the CDN Dolla
option????? See Below.



Sub CurrencyPick()
Dim Currencies As String
Dim CurrencyFormat As String

Currencies = Range("CurrencyType")


Select Case Currencies
Case Is = "USD"
CurrencyFormat = CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"

Case Is = "CDN"
CurrencyFormat = "$""CDN"" #,##0_);[Red]($#,##0)"

Case Is = "GBP"
CurrencyFormat = "£ #,##0_);[Red]($#,##0)"

Case Is = "Euros"
CurrencyFormat = "€ #,##0_);[Red]($#,##0)"

Case Is = "Yen"
CurrencyFormat = "¥ #,##0_);[Red]($#,##0)"

End Select

Range("IncomeStatement").NumberFormat = CurrencyFormat
Range("BalanceSheet").NumberFormat = CurrencyFormat
Range("CashflowStatement").NumberFormat = CurrencyFormat


End Su

--
Message posted from http://www.ExcelForum.com

I found the error on my end:

Select Case Currencies
Case Is = "USD"
CurrencyFormat = CurrencyFormat = "$""US"" #,##0_);[Red]($#,##0)"


Thanks again for your brilliance!!!! Works fine now

--
Message posted from http://www.ExcelForum.com

it's a string.. you'll have to pad it with double quotes.
Furthermo in vba the [] are shortnotation for the evaluate method

CurrencyFormat = "[$$-409]#.##0,00;[Red][$$-409]#.##0,00"

afaik the languageID(409) is not used with excel97

Following will always give you "US$", the \ denotes "literal
interpratation of the following character.
Else the character is interpreted agains local settings (
and for example the U means Hour(s) in Dutch)

CurrencyFormat = "\U\S\$#.##0,00;[Red]\U\S\$#.##0,00"


See VBA help on NumberFormatting







keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


ExcelMonkey wrote:

I want to pass a number format to a variable. The following code is
not
working:

Sub CurrencyPick()
Dim CurrencyFormat As String
CurrencyFormat = [($"US" #,##0_);[Red]($#,##0)]

I am getting a type mismatch error. I think there is a problem with
either a lack of quotes or brackes. Or my variable declaration is of
the wrong type.

Can anyone tell me what wrong here?

Thank-you.


---
Message posted from http://www.ExcelForum.com/

yeah that works.Thanks. How do I get rid of the decimal points?

Thank

--
Message posted from http://www.ExcelForum.com

ah.. oops... copied that the dialogbox, so it's the NumberFormatLocal
string... and since I live in Holland it's not what YOU want.


Just reverse the , and . and you should be fine.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


ExcelMonkey wrote:

yeah that works.Thanks. How do I get rid of the decimal points?

Thanks


---
Message posted from http://www.ExcelForum.com/