change the currency symbol dynamically

[Pilot5000]

I found an even easier way.


I created two columns:

FX Name FX Symbol


Listed out all of the names and symbols in a worksheet, named the range fx_rates


then when the user selects the currency they want to convert to, the user chooses the currency in the pull down menu, to convert all numbers to the appropriate currency.


then in a cell ("E3") in the FX rates worksheet, i do a vlookup to match the appropriate symbol,


then call the sub to apply the format to all of the cells that i want converted.


Sub Currency_Calculate()

Currency_Symbol = Worksheets("FX rates").Range("E3")


Worksheets("Calculator").Range("F11:N49").NumberFormat = Currency_Symbol & " #,##0.00"


End Sub


fast and simple and not a lot of coding. in addition, if i ever do any updates, i won't have to refer to the VBA code, but rather my named ranges.

dear Luke M

i read your post and i liked it very much i am presently in middle of project that deal with the same problems , i tired to work with your explanation but something is not working i get the message "unable to set the number format property of the range class " when i tried to run the macro , therefore i will appreciate if you can create a sample of your post in worksheet and upload it so it will become more clear, thank you in advance and i hope i am not asking for too much ............

good news it work wonderful i love that idea , thank you for the good job and sorry for the disturbance

 

[Luke M]

Hi Pilot5000,

I'm glad you liked my post, but in the thread you refer to, I didn't really do anything. Can you upload an example of your workbook, along with the code/macro that you are trying to use?

 

[Pilot5000]

Hi Pilot5000,

I'm glad you liked my post, but in the thread you refer to, I didn't really do anything. Can you upload an example of your workbook, along with the code/macro that you are trying to use?

thank you for your respond ,
I have a worksheet with multiply columns and range with various data some columns contain percentage and some contain currency, and some text , because this project need to function in several different languishes , the currency format need to be adjusted according to the country it used . Therefore I am looking for a VBA code that can identify and do two things,
1. If the sheet is HorizontalAlignment = xlLeft (i.e left to right ) currency’s symbol should be $ or € , and if the sheet is HorizontalAlignment = xlright (i.e right to left ) currency’s symbol should be ₪ character code : 20AA Unicode (hex)
2. In the place where is % and/or text formatting do nothing ,

i am sorry i didn't upload the file for you because every time i choose the Upload file option i can only see all my files that on my desktop except the excel files
is there is any other way to send you the file ??
Thank you all in advance

 

[Deepak]

http://chandoo.org/forum/threads/ho...-an-xml-file-based-on-certain-criteria.23703/