Change Currency Symbol on Worksheet based on cell value

How do change the symbol of cells that have format currency in euro to dollar based on cell drop list euro/dollar. Need to say that need something to apply to whole workbook that has about 700 worksheets and that sheets have cells formatted as currency, numbers and percent. So I need something that looks at value of drop list cell, then looks for cells formatted as currency in whole workbook then change symbol from € to dollar our vice versa. Don't know how to do this. The most similar thing I found was to change on a range of specific worksheet. And I need something to the whole workbook and to change only the cells formatted as currency whiteout me telling what range is.

It doesnt do anything the location. Just click download and a confirmation letters and numbers box appear to confirm download. And then it download the file. But if you want i upload it here... just dont really see that option under reply. Can i send it to your email or something?

Ahhh... found it lol here it is!

Originally Posted by mrice

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Already did... found it after responding to your last post. Check previous replies.Thank you

Originally Posted by mrice

OK - paste the following into the Sheet1 tab in the VBA editor.

Please Login or Register  to view this content.

man you're a genious! it almost worked perfectlly. The problem is that with Pounds i'm getting Euro symbol (like €10,00) insted of £. But i guess its a formatting problem in VBA code. The real problem is that the code is changing every cell formated as number, day and currency! I need that it changes the symbol of ONLY cells formatted as currency. Is that possible? Thank you very much for your quick replies!

The POUND thing i just figured it out. Just modiffied the code to: Case Is = "POUND"
Cell.NumberFormat = "£#,##0.00"

And now its good. The problem is that changing the currency list changes all numbers cells formated as numbers, date or currency. I want it changes only the ones formatted as Currency and leave the other ones without any change... maybe its not possible...

Now it does nothing

Man youre an angel! I didnt notice any difference between the previous code you sent me and i sad it does nothing. Maybe i just putted it wrong previous. Now it works perfectly. I think this thread will help lots of people because i've search a lot of forums, posts everithing on google and all i could foud was change by a range and not like this. As for your help i'm doing what you ask on your signature please consider donating to Cancer Research UK or your local equivalent". Thank you very much. PS - sorry my english but i'm from Portugal.

It funny though if i put your code into my file it doesnt work. Any idea why?

Found it. Has to do with formatting currency. Changed for € Portugal and now the code works just fine. Thanks a lot!

Sorry disturbing you again but found another problem.... The code works perfectly but only in cell that i put the numbers. Let me explain: if i put numbers in cells and format them as currency, when i change the currency in menu sheet they change too, but if i make another cell with formulas that for example sum all values (currencies) and that cell is formatted as currency too it doesnt change to the selected currency. You can see what i'm saying in the file attached. In menu sheet i have Amount A and Amount B and above Total (which is the SUM of this 2 values). This Total cell is formatted as Currency too, but if i change the currency all formatted currency cells change except the ones that have formulas inside even if they are formatted as currencies. Need to say that formulas can vary and are present in the whole workbook...

One word only: GENIOUS!! That's what you are :D Thank you very much. PERFECT!!!

Thank you. Didnt notice that. I'll be more careful next time.

SOLVED IT :D :D :D

After thinking a little bit i figured it out. As the previous edits i made in the previous post i sad that the problem was with the code of vba for € format. As my currency is formated in € and not € Portuguese (Portugal) as the vba code refers, i though of saving a macro and select the simbol € as formating in currency to see what code excel shows in vba and what i got was a simple

Please Login or Register  to view this content.

So my cells currency predifinition in the workbook are € with the above code and not the one provided with the excelent macro that mrice build.
So i made a minor change to the code he gave and voilá

Please Login or Register  to view this content.

Now empty cells change and the other ones that refers values from other cells (C10 and C11) change too, because now macro can find the € symbol that i was using in the workbook. Sometimes we have to think and do a little of work too and not wait to profissionals like mrice and many others in this forum do all the work for us. We have to lern a little bit from their knowledge too.
Really happy to solved this.
Thank you a lot mrice. You were a great help. Without you i would never get there. Many thanks.

This macro is really slow. let it run all night and not finished yet. Any ideas to make it faster?

I dont mind having a slow macro since it not takes the whole night to finishes the process. it still not finished. Come on people this is the last thing i need to finish my workbook. This macro works as it is now but is extremely slow. How to make it faster? Alternatives. This macro is so slow that it actually freezed my computer. In small workbooks like the sample i put in this topic it works but for my workbook forget it. Impossible to use.

@mrice

Thank you foryour response. Tested this alternative and seems faster than the previous but only tested in the sample book. Then i'm gonna proceed to test it with my workbook. Meanwhile i stard to record some macros and came across with this solution made by me that seems to work. But once again i tested it only in this workbook i built and it works just fine too. Gotta test it to in my workbook to see if its faster than the macro you provided. Gonna leave here this sollution i came up with so you can see and may help anybody who has this problem too. Thank you

@mrice

Yoir macro is still very lengthy. I had to force close excel because it was running the macro for hours. My solution also didnt work because I came up with a message from excel saying "that he could not complete the task with the avaiable resources". Then I closed it and try again and now the macro (which uses find replace dunction format) is giving error 1004 in vba. I close the workbook, open the sample woth that macro and the buttons I built and giving the same error again. It had worked perfectly before I tried to run it in my workbook ans now is gibong this error. Really don't understand. Take it work again I had to record the same macro manually doing the find/replace format thing. I giving hope this will ever work... gonna try one last thing. As my problem is that the first macro that mrice provided don't change format of the cells that as currency format on them but have nothing written, I though now and some commands to this macro so before she runs it creates 0 values entries to these empty cells then the macro would change their currency too (now because they are written) and then another command to erase the content of these cells again. Now the format currency of these cells should be changed. At least thats my hope. Gonma try it and see if it works.

Its not possible because i wolud have to aply conditional format to 365 sheets and for each sheet 500 rows at diferent ranges... so it woulb be possible but it would take a massive lenght of time... I did the other thing i said that i would try, that was built a macro to input values at the ranges of the empty sheets and now they changed and the format manteined. After the conversion another macro runs that erases all data inputed by the previous one. Not the best solution (because i didnt aplied this at the 365 sheets because the same reason i dont want to aply conditional format) but i did this to the month sheets (whitch are the most important ones) and it works. Not perfect, because i would like to have really all workbook changed but thats acceptable. Many thanks for your help mrice. Without you i wouldnt made this far. Really aprecciate your help.
Regards from Portugal.

I've just stumbled upon this thread as I attempt to solve a similar problem albeit in a smaller spreadsheet that Taislin appears to be wresting with.

Is there a way of identifying just those cells that are already formatted as currency (of whatever symbol) and then apply a new symbol or preferably a three letter international currency code? I have coded something using ranges but as the currency cells are dotted around the sheet it's impractical to code in every cell reference. I was looking for something that looked for cells already formatted as currency then changed the symbol or abbreviation with the one from a selection list that the user can select.

I think the attached should illustrate what I'm trying to achieve. The test would be once the code is in place, I could add a number, format it as currency and then make a different currency selection in cell A1 that would change all currency cells including the new one, to the chosen currency.

Thanks


J

Argh, sorry about that, I'll start a new thread :-)

J