Exchange in excel

[Marcelino]

Hi,

I create an excel document, where i write how much money i've paid. I write numbers in euros. My problem is, that i want a macro, whose exchange all the numbers into other currency, for example: dollars.

I want use 1 toggle button. When button is pressed all the numbers are in dollars, and when togle button is unpressed, all the numbers are in euros.

Can you help me?

...sorry for my bad english

THank you very much

 

[Marcelino]

It works great for my example, but HalfAce you are right. In my real document are lot of numbers, which aren't only in column B and there are lot of cells where are formulas too.

I can specify where are only numbers and where are formulas if you want.

Cells with numbers: B5:F35

Cells with formulas: C3:E3;H6:H17;J6:J17;L6:L13;N6:N13;O6;Q6

 

[HalfAce]

Cells with numbers: B5:F35
Cells with formulas: C3:E3;H6:H17;J6:J17;L6:L13;N6:N13;O6;Q6

Well, if you're converting the cells that contain formulas then we'll have to try & change a few things in the code to retain them as formulas. (yes?)

Now since the range we're really working with is different, where is the exchange rate (originally in C10) going to be entered / found?

And which range(s) of the ones mentioned in the quote are going to get converted back & forth?

 

[Marcelino]

Well, if you're converting the cells that contain formulas then we'll have to try & change a few things in the code to retain them as formulas. (yes?)

Yes


Exchange rate can be in cell S4. And numbers, which are going to get converted are in range B5:F35.

 

[HalfAce]

Hi Marcelino,
Good new is, I think I have the code all figured out to convert fixed values and convert & retain the formulas. (I just need to set up a test Wb using all the new ranges so I can make sure it's all good before giving you something that doesn't work right.)

Bad new is I have to go for now and very likely won't be able to continue work on this until tomorrow. Hope that's not a problem.

I'll get to it first chance I get in the morning.

 

[Marcelino]

Hi HalfAce,

Yes, it's ok, it isn't urgent

And i want ask you..It's possible, that formula will be active for more than one sheet in a document? (For three sheets for example, but not for all sheets)..?

 

[HalfAce]

We have more than one sheet to do this on?
Is the layout the same on all sheets? or are you looking at different ranges on the other sheets?
Will you be running the code for one sheet at a time, or are you going to want to do all the sheets in a single execution?

 

[Marcelino]

In my document are 7 sheets. In 6 sheets is the same layout. And in the seventh sheet are only cells, where are formulas, but it isn't finished yet, so i don't know define range of these cells.
And i want the code do all the sheets in a single execution.

 

[HalfAce]

What are the sheet names that you want to perform this on?

 

[Marcelino]

June
July
August
September
October
November
December
Graphs

 

[Marcelino]

HalfAce,

Sorry but i have last appeal for you... Can the currencies look like "5,85 €" and "5,85 $" ? (The values of numbers are only examples)