Changing Currency Symbol based on cell value - unicode symbols

fangfacekitty

Board Regular
Joined
Jul 28, 2010
Messages
63
Depending on the 3 digit currency code entered in cell K9 I need to be able to format a range of cells (F18:L25) to reflect the currency used. For currencies such as USD or EUR, where there is a key on the keyboard to use I have no problems. Nor is it a problem for currencies that use western characters such as IDR being Rp.

My problem is where there is a non-western character. Example, the RUB symbol is руб and while I can manually insert it into a cell I can't get the VBA to code the "б" character, it keeps copying as "?". It has a unicode (hex) value of 0431.

How can I get the macro to return the special character? Thanks in advance for any help!

Below is the code I am using:

Select Case Range("K9").Text
Case "EUR"
Range("F18:L25").Select
Selection.NumberFormat = "€# ###"
Case "USD"
Range("F18:L25").Select
Selection.NumberFormat = "$#,###"
Case "IDR"
Range("F18:L25").Select
Selection.NumberFormat = "Rp # ###"
Case "RUB"
Range("F18:L25").Select
Selection.NumberFormat = "py? # ###"
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

u0107

Board Regular
Joined
Dec 18, 2002
Messages
154
If you were able to solve this problem, could you please share your solution?

Thank you in advance.

Uttam
 

fangfacekitty

Board Regular
Joined
Jul 28, 2010
Messages
63
I was not able to solve the issue completely, I cannot get the special symbols in all cases. I do have a solution that at least indicates the chosen currency though (Case 4 is for the RUB mentioned in my original post) :

Select Case Range("K10").Text
Case "1"
Range("F21:r28").Select
Selection.NumberFormat = "####"
Range("H38:r38").Select
Selection.NumberFormat = "####"
Range("A1").Select
Case "2"
Range("F21:r28").Select
Selection.NumberFormat = "#,##0 [$€-40C]"
Range("H38:r38").Select
Selection.NumberFormat = "#,##0 [$€-40C]"
Range("A1").Select
Case "3"
Range("F21:r28").Select
Selection.NumberFormat = "[$Rp-421]#,##0"
Range("H38:R38").Select
Selection.NumberFormat = "[$Rp-421]#,##0"
Range("A1").Select
Case "4"
Range("F21:r28").Select
Selection.NumberFormat = "#,##0[$p.-419]"
Range("H38:R38").Select
Selection.NumberFormat = "#,##0[$p.-419]"
Range("A1").Select
Case "5"
Range("F21:r28").Select
Selection.NumberFormat = "[$IRR] #,##0"
Range("H38:R38").Select
Selection.NumberFormat = "[$IRR] #,##0"
Range("A1").Select
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
My problem is where there is a non-western character. Example, the RUB symbol is руб and while I can manually insert it into a cell I can't get the VBA to code the "б" character, it keeps copying as "?". It has a unicode (hex) value of 0431.
...
Case "RUB"
Range("F18:L25").Select
Selection.NumberFormat = "py? # ###"
...
Hi

You can get a Unicode character in vba using ChrW(). For the example you posted, try:

Code:
...
Case "RUB"
        Range("F18:L25").NumberFormat = ChrW(&H431) & " # ###"
...
Remark: you don't usually select objects in vba
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Sorry, I just posted code for the letter you could not display. I guess you also want the others. :)

Code:
        Range("F18:L25").NumberFormat = ChrW(&H440) & ChrW(&H443) & ChrW(&H431) & " # ###"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,139
Messages
5,466,910
Members
406,506
Latest member
donwiss

This Week's Hot Topics

Top