Solved: Apply exchange rate to many cells

[hunsnowboard]

Hi there Everyone!

I was wondering if there is a faster solution for my problem. I have a workbook which has around 20-25 worksheets. The worksheets contain (besides text) more than 1000-2000 numbers in different cells. These numbers are amounts in USD currency. Now, I need to change this currency to Euro. So I have to divide each cell containing numbers with the given exchange rate. Is there a faster way than doing it manually one by one?
Thank you in advance for your help!

[jolivanes]

Yes there is but without you telling us where all these amounts to be converted are located, it is impossible. Are these numbers in one column or scattered all over? If they are all over, can you move them in one column? Which sheets have the amounts. Do all sheets have to be converted? If not, which ones not. Etc etc.
Do you want a formula or code?

John

[macropod]

Hi hunsnowboard,

Do you want a flexible solution that allows the USD and exchange rates to be varied, with the converted values in an adjacent cell? Do you want values rounded off to the nearest Euro cent or Euro? Or do you want a one-off conversion with the updates applied directly to the USD cells?

[hunsnowboard]

Hi Everyone!

Sorry for being too brief.

So, answers to the questions:
The amounts all scattered all over the place, in many many columns and rows. Unfortunately I cannot place them in one column or row, actually I wouldn't like to move them at all.
All the sheets have to be converted, but if there is a quicker solution for one sheet, I will do it for all the sheets.
The amounts are not in the same cells on the sheets. So on sheet1 the numbers are in different cells compared to sheet2.
I am looking for the most simple solution, so if it's easier with a formula than formula, but if code is easier/faster than code.
I do not need the Euro amounts to be placed in the adjacent cells. I just need a formula/code which converts the amounts with the given interest rate.
I need the amounts in closest Euro, no cents needed.

Thank you in advance!

[macropod]

Hi hunsnowboard,

The following code multiplies all numbers by the nominated exchange rate. Simply change the '1' in 'xchg = 1' to suit.
Sub EuroConverter()
Dim xchg As Single, ws As Worksheet, ocel As Range
Application.ScreenUpdating = False
xchg = 1
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
For Each ocel In ws.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(ocel.Value) Then ocel.Value = Round(ocel.Value * xchg)
Next
Next
Application.ScreenUpdating = True
End SubUse with care because, after rounding has been applied, there's no relaible way of retrieving the original values.

[hunsnowboard]

Hi Macropod!

Thank you very much for your solution! It works great! However there is a small addition I would like to add.

I would need this macro to work on the selected worksheet and on a selected range.

So I select a range on the worksheet and run the macro. The changes should be applied only in the range selected by me. Could you please help me with this?

Thanks a lot in advance!

[macropod]

Try:
Sub EuroConverter()
Dim xchg As Single, ocel As Range
Application.ScreenUpdating = False
xchg = 1
On Error Resume Next
For Each ocel In Selection
If IsNumeric(ocel.Value) Then ocel.Value = Round(ocel.Value * xchg)
Next
Application.ScreenUpdating = True
End Sub

[hunsnowboard]

Hi Macropod! Thank you very much! It is working like charm! Thank you!