Covert all values in sheet containing dollar values to another character.

[niceguy21]

Hello,

I currently have a macro enable workbook sheet that has a lot of values and calculations performed on it with dollars.

What I would like is a macro that identifies all (non-formula containing) cells formatted with dollar values and performs a currency markup on them:

there will be a dropdown in cell A1 that allows the selection of the desired currency:

I.E If you desire to change all these values to pounds.. pound is selected from drop-down and all these cells are reduced; multiplying by 0.6, and assigned a new format (£) symbol.

I can take care of the formula containing cells; these can have an added variable added, alongside conditional formatting.

I would be amazed if there exists such a piece of code!!

Many Thanks,

M

[stnkynts]

Your post is lacking a lot of details but I put something together that will probably get you on the right track. You will need to edit this code to match what you want to do, and even with that I can forsee some issues. Best of luck.

Please Login or Register  to view this content.

[niceguy21]

Your post is lacking a lot of details but I put something together that will probably get you on the right track. You will need to edit this code to match what you want to do, and even with that I can forsee some issues. Best of luck.

Yes it is a tricky one.. but your solution appears all encapsulating. I take it this code detects any changes in A1, (say its "EUR") and in the case of the new value being "$" converts all x1celltypeconstants on the entire sheet? and multiplies them through:

Please Login or Register  to view this content.

etc. and changes formatting by:

Please Login or Register  to view this content.

[niceguy21]

Your post is lacking a lot of details but I put something together that will probably get you on the right track. You will need to edit this code to match what you want to do, and even with that I can forsee some issues. Best of luck.

Yes it is a tricky one.. but your solution appears all encapsulating. I take it this code detects any changes in A1, (say its "EUR") and in the case of the new value being "$" converts all x1celltypeconstants on the entire sheet? and multiplies them through:

Please Login or Register  to view this content.

etc. and changes formatting by:

Please Login or Register  to view this content.

[stnkynts]

Precisely. So you can see one of the problems that will occur if you try to change back and forth between the value in "A1". It will just keep multiplying the values higher and higher. There are many ways that you can correct this but it really depends on your sheet. Let me know if there is anything else I can do to help.

[niceguy21]

Precisely. So you can see one of the problems that will occur if you try to change back and forth between the value in "A1". It will just keep multiplying the values higher and higher. There are many ways that you can correct this but it really depends on your sheet. Let me know if there is anything else I can do to help.

would you like to see an example sheet?

Many Thanks!

[stnkynts]

would you like to see an example sheet?

Sure. That way many people can look at it and I am sure one of us will be able to come up with a solution.

[niceguy21]

sample.xlsm

Sure. That way many people can look at it and I am sure one of us will be able to come up with a solution.

Please find a working example attached above.

You will see how the worksheet functions.. the input values need format change + value change, and all the other formulated cells just require format change. I have highlighted these in yellow and green respectively.

Let me know your thoughts.

Thanks!

[stnkynts]

Make sure you do this:

- Unmerge Sheet (Inputsheet) Range K7 & K8
- Post the below code under Sheet2(Inputsheet) in VBA....not "This Workbook"
- You will need to change the coversion factors to whatever you need them to be. It is set to recognize the last value and adjust to what you specify.

Please Login or Register  to view this content.

[niceguy21]

Make sure you do this:

Many thanks for that!! that is such a great step forwards!


unfortunately, the values keep getting larger and larger as you rotate though the currencies.. it isn't adjusting back.

Also, I am having issues with the formatting in column "L". the symbols aren't updating correctly?

Many thanks!

[stnkynts]

The values are getting larger because you didn't put in the proper conversions. Essentially you are going to have an old conversion value say "dollars" and a new conversion value, say "euro". For explanation sake lets say the conversion is 1 dollar to 2 euro. The oldValue would be "dollars" (found under Select Case oldValue) but you would need to look at the (Select Case for Range("K7")) which would be the new value "euro". In that body is where the conversion of 2 would go.

Looking at the other side (ie going from euro to dollar) you would have an oldValue of "euro" and a new value (Select Case Range("K7")) of "dollar". The conversion factor there would be .5. That way you could flop back and forth between the two and still have the same value.

Its probably difficult for you to see where to put this stuff. I tried to write the code in a way that was easy to recognize what is transpiring but even still it probably requires some time to fully grasp what is going on. In terms of column "L".....works fine for me when I test it.

[niceguy21]

The values are getting larger because you didn't put in the proper conversions. Essentially you are going to have an old conversion value say "dollars" and a new conversion value, say "euro". For explanation sake lets say the conversion is 1 dollar to 2 euro. The oldValue would be "dollars" (found under Select Case oldValue) but you would need to look at the (Select Case for Range("K7")) which would be the new value "euro". In that body is where the conversion of 2 would go.

Looking at the other side (ie going from euro to dollar) you would have an oldValue of "euro" and a new value (Select Case Range("K7")) of "dollar". The conversion factor there would be .5. That way you could flop back and forth between the two and still have the same value.

Its probably difficult for you to see where to put this stuff. I tried to write the code in a way that was easy to recognize what is transpiring but even still it probably requires some time to fully grasp what is going on. In terms of column "L".....works fine for me when I test it.

Yes, looking at your code I am understanding your thinking with regards to having a "circular" set of exchange rates, such that when one toggles, they keep coming back to the same number. This present an issue of practicality here in my office as users will have to constantly insure this is kept up to date.

I still am a little confused by the formatting however. I will revisit this post soon and try to get it complete.