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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,771
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,771
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,095,188
Messages
5,442,914
Members
405,206
Latest member
Neetish_93

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top