MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to convert multiple cells from one currency to another


Posted by Denis on September 07, 2001 1:43 AM

Hi
I have a full page of figures and i would like to have a macro to convert them from one currency to another and then back again if desired. Can anyone help, or point me in the right direction?
Many thanks
Denis


Posted by Robb on September 07, 2001 4:40 AM

Denis

Do you mean formatted in another currency or actually recalculated using an exchange rate?

If the latter, would I be right in assuming the exchange rate would be subject to change?

Regards

Posted by Denis on September 07, 2001 5:08 AM

Robb
Yeah, i would like the area which contains the figures (say £) to be replaced by figures in another currency - depending on the macro selected. The exchange rate would definitely need to be modifiable.
Thank you.
Denis

Posted by Robb on September 07, 2001 5:59 AM

Denis

Here are 2 lots of code, written for CommandButtons but you could
use it any way you like. Given the number of different currency symbols, I have not
formatted to show them, just the amounts.

Also, since people quote rates in different ways, I have indicated which one multiplies by the rate and
which one divides. Rates are input by way of Input Boxes.

All the figures in Sheet1 will be converted - of course you may amend the sheet names.
Private Sub CommandButton1_Click()
'Multiply by the exchange rate
getrate = InputBox("Enter the current rate")
If getrate = "" Then Exit Sub
userate = CDec(Format(getrate, "0.0000"))
For Each c In Worksheets("Sheet1").UsedRange.Cells
If Not c = "" Then c.Value = Format(c * userate, "0.00")
Next c

End Sub

Private Sub CommandButton2_Click()
'Divide by the exchange rate
getrate = InputBox("Enter the current rate")
If getrate = "" Then Exit Sub
userate = CDec(Format(getrate, "0.0000"))
For Each c In Worksheets("Sheet1").UsedRange.Cells
If Not c = "" Then c.Value = Format(c / userate, "0.00")
Next c

End Sub

Any help?

Regards

Posted by denis on September 10, 2001 8:41 AM

Robb
Thanks for that, i will give it a try. For the moment i have just created a second worksheet and linked the formulas through there. Many thanks for your help.
Denis