VBA to find and change currency values in workbook

[MunichMag]

I have a workbook where I'd like to change the currencies between USD & Euro (and back again) easily.

I have a code (below) to change the format, from $ to € however, it doesn't change the value. Changing $100 to €100 is wrong as the initial value should take into account an exchange rate. So, I'd like $100 to change to €94 depending on the exchange rate that I enter into a specific cell.

Here is the code I'm using to change the format:

Sub CurrencyFormat()
Dim theSheet As Worksheet
For Each theSheet In ActiveWorkbook.Sheets
Application.FindFormat.NumberFormat = "[$$-en-US]#,##0"
Application.ReplaceFormat.NumberFormat = "[$€-de-AT] #,##0"
theSheet.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
Next
End Sub

Any help would be greatly appreciated!

 

[JoeMo]

Hi JoeMo,

Yes, I copied and pasted your code into multiple different test workbooks as well as typing manually and changing the formats etc.

An interesting additional point is that after closing and saving the workbook, I cannot run the code when I re-open the workbook I just get a run-time error '1004' related to the find / replace formats. These formats run fine before saving the work book and can only be rectified by coping and pasting in the exact same custom formats. This happens for your code and any other currencies I try it with.

I've tried multiple different ways and the only time it's ever worked is if I use value instead of NumberFormat.

Are you able to share your test worksheet with me?

Looking back at what I did to test the code, I found that I had to replace your formats with ones that work for US country settings. Maybe the problem is with the find format or possibly both formats you are using.