Currency formatting based on three letter code in cell

[albasheer]

Hello,

How can I custom format a cell to display the currency three-letter formatting based on an input in another cell.

For example in Cell A1 I would enter ZAR and the format would be applied to a certain range.

I am thinking it would be some sort of code applied to a button to press after setting the currency.

The custom format is _([$SAR] * #,##0.00_);_([$SAR] * (#,##0.00);_([$SAR] * "-"??_);_(@_)

Thank you.

 

[rs2k]

Have a look at Select Case.

How many currencies are you dealing with and what range is the data in?

Colin

 

[albasheer]

I have a costing model that is generated in USD. The summary sheet has the figures in 1 column in USD and in the next column in some other currency depending on what country our business extends to (no less than 5 currencies, but only one currency is chosen).

 

[rs2k]

Have a look at this;

Sub CurrFormat()
Select Case Range("A1").Text
Case "USD"
Range("C2:C11").Select
    Selection.NumberFormat = "[$$-409]#,##0.00"
Case "YEN"
Range("C2:C11").Select
    Selection.NumberFormat = "#,##0.00[$?-450]"
Case "Euro"
Range("C2:C11").Select
    Selection.NumberFormat = "#,##0.00 [$€-1]"
End Select
End Sub

I used the value in A1 for the select case. Depending on which value is chosen, it will go through the case statements untill one matches and then run the code under it. I used 3 currencies to test this, (I used YEN, but it doesn't use the YEN symbol, I couldn't find it, so just picked one at random).
You will have to apply the formats you want.

HTH
Colin

 

[albasheer]

Thank you. This is great help. I have tried searching the Internet for help on the numeral part of [$$-409] but wasn't successful. Any sources I can consult for that and other help with how to format numbers/currencies using VBA?

 

[rs2k]

Probably the easiest way to find the formatting is to record a macro and see what VB does.

If you are not familiar with recording macros, do this;
Enter your numbers in a cell, then go to Tools>Macro>Record New Macro, give it a name or choose the default one it gives and click OK.
Do whatever formatting is required to the numbers then stop the macro (if the macro button isnt on your desktop, then just follow the instructions for starting it and you will see a stop recording option).
Then go to Tools>Macro>Macros and choose the macro you just recorded and choose the edit option. This will open up the VBE and you can see how VBA interpreted your formatting. Obviously recording macros isnt just for formatting, it can do a whole lot more other stuff as well, it's good for finding the correct syntax when you are struggling.

HTH
Colin

 

[albasheer]

Thanks a lot Colin. I achieved the results I wanted. I was just wondering about the 409 in the format. Thanks again.

 

[rs2k]

I have absolutley no idea what the 409 relates to, probably a serial number for that chosen format, who knows

Cheers
Colin

 

[PuckMark]

I realize I'm resurrecting an old thread, but this is exactly the problem I am trying to solve. I am using Excel 2003, and this code is not working for me at all. Can anybody tell me what I might be doing wrong? Will this not work in Excel 2003?

Thanks,
Mark

 

[PuckMark]

Just for reference sake, here is the code I'm using, and it doesn't seem to work in Excel 2003:

Sub CurrFormat()
Select Case Range("A1").Text
Case "USD"
Range("C2:C11").Select
    Selection.NumberFormat = "[$$-409]#,##0.00"
Case "YEN"
Range("C2:C11").Select
    Selection.NumberFormat = "#,##0.00[$?-450]"
Case "Euro"
Range("C2:C11").Select
    Selection.NumberFormat = "#,##0.00 [$€-1]"
End Select
End Sub