Currency formatting based on three letter code in cell

albasheer

Board Regular
Joined
Dec 14, 2009
Messages
159
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.
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Have a look at Select Case.

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

Colin
 
Upvote 0
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).
 
Upvote 0
Have a look at this;
Code:
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Thanks a lot Colin. I achieved the results I wanted. I was just wondering about the 409 in the format. Thanks again.
 
Upvote 0
I have absolutley no idea what the 409 relates to, probably a serial number for that chosen format, who knows:confused:

Cheers
Colin
 
Upvote 0
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
 
Upvote 0
Just for reference sake, here is the code I'm using, and it doesn't seem to work in Excel 2003:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,482
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top