Because the value in the Currencies variable is a literal string you
have to surround it in double quotes just as you would any other literal
string in order to get it into a number format. Here's an example:

Case Else
CurrencyFormat = """" & Currencies & """ #,##0_);[Red]($#,##0)"

--
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 am passing a currenct number format to a variable and using applying
this format to a range. The number formats themselves are driven by a
Case Statement. If the currency chosen equals one of five specific
currencies I pass one of 5 specific formats to a variable. I want to
put a 6th condition in the Case Statement that says, If the currency
equal something other than the five prescribed Cases, then pass the
text string into the format itself. I am using a Case Else statement
for this 6th condition. I just do not know how to incorpoate the
variable "Currencies" into the format string. Does anyone know how to
do this?

See below:

Select Case Currencies
Case Is = "USD"
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)"

Case Else
CurrencyFormat = Currencies & " #,##0_);[Red]($#,##0)"

End Select


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