Currency Formatting macro!!!!!

[marvinabou7890]

Hello everyone.
Hoping someone here can help me with my latest problem. So I have a macro that converts the currency of all input parameters in to my model. However I was unable to convert the formatting as well. When I convert from say dollars to Euros I need the symbol next to the figure to change as well. I will post my code below but what I really need is just a macro method of switching between currency formatting without having to define every single cell in the model because the model is kinda large. I am hoping there would be a way I guess look through the model for a every cell with currency formatting then format it to the correct currency. As of now I am able to do it but I basically just put conditional formatting formulas on every page which get very annoying. Any ideas are helpful.
Thank you guys,
Your friend

[marvinabou7890]

Please Login or Register  to view this content.

[ptmuldoon]

Can you post a sample file of the code? Your looping alot of the same date repetively and I think it may be something that be made a little cleaner with a case statement or other.

[marvinabou7890]

HI,
attached is the model I took out a lot of stuff but this is all you guys should need if you have any questions please let me know.
Thank you,
Your friend

[marvinabou7890]

The macro is activated by a button on the inputs sheet

[Norie]

Not 100% sure I follow what you want to do but try this.

Please Login or Register  to view this content.

[marvinabou7890]

Hi,
Wow that code is fantastic it works for the currency conversion, its waay simpler than my macro. However what I need is something in the macro that converts the currency and changes the formatting to show say a euro symbol instead of a dollar symbol. It would be fantastic if there was a way for the macro to basically find any cell in the whole workbook that has currency formatting than change that formatting to the correct currency it was just converted to. IDK any idea.
Thank you,
Your friend.

[MrShorty]

For a truly "global" option, you can go into Window's control panel and change the default currency symbol. This will change the default currency symbol that Excel uses in all open workbooks.

If it doesn't need to be a macro, changing the currency symbol in a cell formatted currency should be as easy as selecting the cell and changing the currency symbol in the Format cells dialog (https://support.office.com/en-gb/art...0-2b54366bc7a4 ).

If it must be a macro, I recorded this macro while changing the to different currency symbols as described in the above help file:

Please Login or Register  to view this content.

It would seem that selecting a currency symbol is as "simple" as figuring out what "code" goes in the brackets, or inserting the desired currency symbol in front of a custom number format. The hardest part of that will be finding the different ANSI or UNICODE codes for the currency symbol you want to use. I usually look them up in Window's Character map (usually in Programs -- Accessories -- System Tools).

Perhaps the harder part of your request is the "find all cells formatted as currency". I'm not sure of any "shortcut" ways to do this. It might be necessary to loop through each cell in the workbook, test the .numberformat property for currency (maybe check to see if it contains "$"?), then change the number format accordingly. If you have a large workbook, that could take a while to test every cell. Is there something else about these cells that could be tested for? Could you identify which ones are cuppsed to be currency beforehand, to eliminate the need to test every single cell?

[marvinabou7890]

Hi,
Thank you for replying. Basically right now I have a reference to what currency the model is in on the top of every page in the workbook and I created conditional formatting formulas for each currency on every worksheet but in a model like the one I am using it can be very very tedious and annoying to select all the currency cells and make a conditional formatting formula around those cells for each currency and each page. Maybe the macro can pick up on the currency on the top of the page and format every cell with a "$" on that sheet then format accordingly? I wouldnt mind the macro taking a while to run as long as it was accurate. Any other ideas?
Thank you,
Your friend

[MrShorty]

This is not the kind of programming that I really do, so I don't really have any other ideas. I don't readily see any alternative to "find each 'currency' cell" and then "change the .numberformat property for each of those cells. Which part do you need "other ideas" on -- finding the currency cells or assigning a value to the .numberformat property?

[rorya]

If you apply a currency style to the cells, all you need to do is modify the numberformat of the style as needed and the cells will automatically update.

[marvinabou7890]

Hi,
Rorya can you elaborate on that a little but?
By any other ideas I mean maybe a more efficient method to do this.
Thank you,
your friend

[rorya]

Apply the Currency style to the relevant cells (remove the Conditional Formatting) then use code like this:

Please Login or Register  to view this content.

[marvinabou7890]

Hello,
How exactly can I sue the above code. Is it its own macro or does it go with the macro I have already written. I have a new currency converter macro that I will post below. What I really need is to be able t change the currency of every cell being multiplied in the macro by the currency it is being multiplied by. Any help would be great.
Thank you,
Your friend.

Please Login or Register  to view this content.

[rorya]

You can call it from any routine you want or on its own whenever you need to update the currency formatting.

[marvinabou7890]

Hello,
I ran the currency formatting macro above on its own. When I do so it comes back with an error on the line

Please Login or Register  to view this content.

I am also a little confused on how this macro works I onyl want the currency formatting to apply to the ranges being converted for example the ones in the currency converter macro above. What would you recommend?
Thank you,
Your friend.

[marvinabou7890]

Hello,
Is anyone still in this thread I still need help?
Thank you,
Your Friend

[MrShorty]

I thought rorya's idea of changing the style was a good one, until you said that it needed to only apply to the cells being calculated and not to all cells formatted as currency.

What part of this are you really stuck on? Is it finding the ANSI/ASCII codes for each currency character? Is it determining what currency you are using?

If I follow the basic idea of your code, I would probably simply add a .numberformat statement to each loop. Using the British pound loop as an example:

Please Login or Register  to view this content.

Could it be done that easily?

[marvinabou7890]

Hello,
WOW that worked bro. Thank you I added a variation of that line to each loop and it works just fine. the code is kinds long but my original macro sucked anyways. Thank you so much. I will post the code below so others might be able to use it. I was wondering now I have a bunch of cells on each sheet that contain formulas these cells were not converted yet are still currency cells would you know of a way to now change the formatting on these when the input cells are converted using VB. I guess I could just define a big range on every sheet and use the same code as below but that is probably inefficient. Any ideas?
Thank you,
Your friend

Please Login or Register  to view this content.

[MrShorty]

Do you know in advance what cells/ranges need to be changed with each one? It sounds like the formulas are already in place, so I would expect you should know which cells are going to need changing. If you do, it should be as simple as

Please Login or Register  to view this content.

where you just need to substitute in the desired Range object(s).

If you cannot know beforehand what cells will change, then you will need a code block that will search and find those cells that are dependent on these cells.

[marvinabou7890]

Hi,
Thank you for a fast reply. That is exactly what I was thinking and will totally work. However I am looking for a more simplistic method I suppose because this is a big model and defining a range of currency cells that contain formulas for every sheet then hard coding it in doesn't seem very efficient to me. I would like to eventually allow this macro to translate over to other models easily without a whole lot of things that need to be hard coded in. I am not familiar with cell block, is there a method to have the macro scan for cells that contain say a "$" sign then change the format in the same as used above. Or any other ideas. I really appreciate all your help.
Thank you,
Your friend

[MrShorty]

is there a method to have the macro scan for cells that contain say a "$" sign then change the format in the same as used above.

There is the range.find method https://msdn.microsoft.com/en-us/lib.../ff839746.aspx If you structure the search correctly, it might be able to find those cells formatted with $, and then you can change the number format. Since this is the equivalent of the built in Find command, you might try it manually first (maybe record a macro while doing it) to see what options need to be present (for example, you may need to search by value instead of by formula). This isn't the kind of programming I do, so I am not familiar with the .Find method, but I know it is there. Gut feel is that, if this is the approach you want to take, this is the method that will do it.

[rorya]

You don't have to use the built-in Currency style. Create a new style that applies the formatting you want, apply that to the cells that should change, and then use that style name in the code.

[marvinabou7890]

Hey guys,
So I had a great Idea I took some code from a different macro I had and I am pretty sure the logic will work. Hmmm I keep getting an error basically I am calling each entire sheet a range and applying an if statement to every cell in the range. The macro I have now doesnt quite work though maybe someone here will know what i am doing wrong. Basically once this piece of code is correct I can apply it to every loop in my currency converter macro.
Thank you,
Your friend.

Please Login or Register  to view this content.

[MrShorty]

What error are you getting? I see an obvious "For without next error" -- there is no Next c statement to end the For Each c...loop.
I also notice that your If c.numberformat... statement seems off. right now, it is saying, "if c.numberformat is dollars currency, then count to 50." It seems like you would want to be doing something 50 times, but there is nothing inside of that for i...Next I loop.

[marvinabou7890]

okay So I finished the model how I did it was just define a name of every range that needed the correct currency then manually add that in to the macro it took a while but it works great. Maybe just going forward if anyone can think of a simpler way of just selecting all cells formatted with a currency and change those formats based off a cell that would be fantastic. That would be fantastic.
Thank you,
Your friend

[rorya]

See post 23.

[marvinabou7890]

Hi
Rorya, Doesn't that still require me to define all the cells that need to be changed as a range then pply that custom type of formatting toeach of those defined ranges. I am trying to accomplish this without having to to tell excel which cells to change. I would like excel to find the cells formatted with any currency symbol then change those cells accordingly.
Thank you,
Your Friend

[MrShorty]

Doesn't that still require me to define all the cells that need to be changed as a range then pply that custom type of formatting toeach of those defined ranges.

rorya seems more familiar with styles, than, but it seems like that would be the advantage of using a style approach -- you would not need to define all of the cells in the range that need to change. You would only need to modify the definition of the style -- specifically, change the currency symbol in the number format code -- that has been previously applied to the cells of interest. After changing the definition, all cells with that style applied, will change their format automatically.

[marvinabou7890]

Hi,
Mr.Shorty I don't really understand when you say modify the definition of the style. Can you maybe show a short example in code?
Thank you,
Your friend

[MrShorty]

rorya's code from post #13 worked just fine for me (tested in attached spreadsheet). I'm not sure why it errored for you. Was there something in that code that you did not understand?

[marvinabou7890]

Hello,
I just tried this same macro again and I got an error at

Please Login or Register  to view this content.

I am not really sure exactly why and the debugger doesnt not give any type of hint. I appreciate all the help.
Thank you,
Your friend

[MrShorty]

I don't know why you are getting an error there. The only thing I see to check is if your workbook has a style named "Currency", though I would have expected mine to have that style.

Maybe try listing the names of every style and see if it has a different name (a simple for each..Next loop should do it)?
Try creating a new style with a name you recognize, so that you know that specific style exists?

[marvinabou7890]

Hi Guys,
So it is still not working for me. I changed it around a little and applied an if statment thought this would work better but it is not changing or doing anything. I will attach the code below. Any help would be great.
Thank you,
Your friend

Please Login or Register  to view this content.

[MrShorty]

Are you familiar with how to step through a procedure one step at a time? If not, it is a very useful debugging strategy: http://www.cpearson.com/excel/DebuggingVBA.aspx

I pasted your code into an empty workbook, renamed a sheet "config & summary", entered "British Pound" into cell E7 and put some random numbers into a range formatted with the currency style. Then I stepped through your code. My first observation is that it completely skipped over the Select Case because cell E7 did not contain the text string "*British Pound*" (note the presence of the asterisks). Since I did not include the asterisks in E7, the If test returned False and it skipped over the block If. By changing the If condition to ws.range("E7")="British Pound" (no asterisks), the code worked just fine.

What is the purpose of the asterisks? Are you wanting to use them as wildcards, because you cannot know beforehand exactly what text will be in E7? I am not certain how wildcards work in these kind of situations, but you may need to consider exactly what you need this to do.

[marvinabou7890]

hi,
Attached is a workbook with some data and currency formatting yet the macro does not work. I am curious if I am missing something very simple.
Thank you,
your friend

[marvinabou7890]

Here you go

[MrShorty]

Very interesting, and I cannot explain all that I see.
One suggestion -- don't say only that a macro "does not work". I think it is much more helpful to explain in as much detail as you can exactly what is not working (error, doing the wrong thing, doing nothing, etc).
The file in post 37 is xlsx, so there is no macro in it. I assumed you are using the procedure in post 34, so I copied that procedure into a module in this workbook (and saved as xlsm).
The first thing I noticed, as I noted above, is that VBA does not equate "British Pound" and "*British Pound*". These two text strings are not equal, so I changed the condition in the If statement to match the string in the spreadsheet. I also removed the #,##0.00 from the sfmt assignment statement, since that part of the number format is in the numberformat assignment statement.

With those two changes, the code ran without error, but column G did not respond to the change in style. I checked the number format of G in the "number format" dialog, and it had not changed.

I made a copy of G in column H, went into the cell styles dialog, and applied the currency style to column H (http://www.excel-easy.com/examples/cell-styles.html ). Note that this is not the same dialog as the "number format" dialog. The numbers were formatted with the euro symbol. I executed the "reset" procedure in module 1, and column H responded to that change, but column G still did not change. I then ran the main procedure, and, again, column H responded to the change, but column G remained unchanged.

So, I added a block of code to look at what style is applied to column G and column H. Interestingly, both columns show the same style name and the same number format code for the applied style, but column G's number format is different from column G's style's number format (if that makes any sense). My guess at this point is that, somewhere along the line, you applied a number format "on top of" or "after applying" the style to column G, so this later number format is overriding the applied style number format.

[marvinabou7890]

Hi guys,
So I am back to this lol I found a temporary solution last time but now i need a permanent one. Mr.Short would i tried so many variations of your macro and I was wondering when I say have a column of numbers say column A with any random currency it does not change the formatting when I run the macro like the column with the cells you formatted did. Are you using some kind of special fromatting? is there a way to make this work with any original formatting and work multiple times like back and for between many different formats like in this macro below. But this macro does not work for any currency it only works on the cells mr.shorty formatted.
Thank you,
Your friend

Please Login or Register  to view this content.

[MrShorty]

Did you check your cells against this scenario?

My guess at this point is that, somewhere along the line, you applied a number format "on top of" or "after applying" the style to column G, so this later number format is overriding the applied style number format.

If you have an "overriding" number format applied to any cells, that will override the number format from the style. The last code I posted changes the definition of the style, which should change the number format of all cells that have that style applied to them, but may not change the number format of cells with an overriding number format.

With all that the history that this spreadsheet has, it might be worth the effort to maybe clear all formats in all cells, then apply the style(s) and see if that helps. Or maybe go so far as to start from a clean, blank workbook, copy values and formulas (not formats) into the clean workbook, then apply styles and run the macro.

[marvinabou7890]

Hello,
I began in a new workbook. I would ideally like to use the macro in my previous comment. I am not exactly sure what is going on there are no overriding formats I am putting in. Basically column A has to be in your custom $0.00 formatting before the macro is ran other wise it does not work. What I need this macro to do is be in any currency format then have all currency cells change formatting to the correct format in the case as defined in the macro. What it does now is basically change the formatting once but only if the cell are formatted in your original $0.00 custom format. Is this making sense or should I attach a new workbook?
Thank you so much,
Your friend

[MrShorty]

You will probably need to upload a new workbook. Without it, all I can say is, anything formatted with the currency style should change number formats when the macro changes the style's number format. My guess is that you are formatting the cells with a currency number format, but not applying the currency style, but we cannot check that without a sample of the file you are now working with.