Changing Currency Symbol based on cell value - unicode symbols

[fangfacekitty]

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? # ###"

 

[u0107]

If you were able to solve this problem, could you please share your solution?

Thank you in advance.

Uttam

 

[fangfacekitty]

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]

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:

...
Case "RUB"
        Range("F18:L25").NumberFormat = ChrW(&H431) & " # ###"
...

Remark: you don't usually select objects in vba

 

[pgc01]

Sorry, I just posted code for the letter you could not display. I guess you also want the others.

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