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!

 

[S.H.A.D.O.]

Welcome to the board,

Would setting up two lookup tables with the relevant exchange rates help?

 

[JoeMo]

Untested - try this on a copy of your workbook. Assumes currency conversion factor (multiplier) is in cell A1 - change cell address (in red below) to suit.

Sub CurrencyFormat()
'assumes currency conversion factor is in cell A1 - change to suit
Dim theSheet As Worksheet, c As Range
Application.ScreenUpdating = False
For Each theSheet In ActiveWorkbook.Sheets
    For Each c In theSheet.UsedRange
        If c.NumberFormat = "[$$-en-US]#,##0" Then c.Value = c.Value * [A1] 'change conversion value cell address to suit
    Next c
    With Application
        .FindFormat.Clear
        .ReplaceFormat.Clear
        .FindFormat.NumberFormat = "[$$-en-US]#,##0"
        .ReplaceFormat.NumberFormat = "[$€-de-AT] #,##0"
    End With
    theSheet.Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
Next theSheet
With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .ScreenUpdating = True
End With
End Sub

 

[MunichMag]

No Dice, sadly.

It changes the currency format but not the value. I also tried it with just "= c.Value * 3" rather than a cell and it still didn't work.

 

[JoeMo]

No Dice, sadly.

It changes the currency format but not the value. I also tried it with just "= c.Value * 3" rather than a cell and it still didn't work.

Only way I can imagine that not happening is if the format isn't exactly right. The format must be exactly "[$$-en-US]#,##0" or the conversion factor will not be applied. Of course, that same format is being used to find the cells you want to reformat in Euros so I'm at a loss to explain why the format changes and not the value. I have run some simple tests and the code changes both format and values for me.

 

[MunichMag]

Hi JoeMo,

The issue seems to be with the "NumberFormat". I replace this code with " If c.Value = "100" Then c.Value = c.Value * [A1] " it all works fine - those values with 100 are changed and the currency formats are changed.

I've used various different currencies in a new workbook then copied and pasted the currency format from the custom category to ensure they are an exact match.

Any ideas? Thanks.

 

[JoeMo]

Hi JoeMo,

The issue seems to be with the "NumberFormat". I replace this code with " If c.Value = "100" Then c.Value = c.Value * [A1] " it all works fine - those values with 100 are changed and the currency formats are changed.

I've used various different currencies in a new workbook then copied and pasted the currency format from the custom category to ensure they are an exact match.

Any ideas? Thanks.

As I said, in a quick test it works well for me. The only thing I can suggest is that you copy the format in the FindFormat line and paste it to the c.NumberFormat = "...." line to ensure both are the same.

 

[MunichMag]

Yes, I've tried copying the exact same formats and it doesn't work for me.

Which part did you try a quick test on? I've tried all parts together and apart and still can't find a solution.

I just can't get this line to work under any circumstances (unless I change it to a value rather than a format): c.NumberFormat = "[$$-en-CA]#,##0.00" Then c.Value = c.Value * [A1]

I'm pulling my hair out here!

 

[JoeMo]

Yes, I've tried copying the exact same formats and it doesn't work for me.

Which part did you try a quick test on? I've tried all parts together and apart and still can't find a solution.

I just can't get this line to work under any circumstances (unless I change it to a value rather than a format): c.NumberFormat = "[$$-en-CA]#,##0.00" Then c.Value = c.Value * [A1]

I'm pulling my hair out here!

Just to be sure, did you copy the code I posted directly from your browser window and paste it into the VBE, or did you retype it? If the latter, I would suggest you overwrite the existing code with a paste of a copy from your browser.

My simple test amounted to placing a dozen or so numbers like 1000, 200, 5000, ... (3000), ... in scattered cells (not contiguous) on a blank worksheet. Then formatting the used range as "[$$-en-US]#,##0" placing 0.94 in cell A1 on that sheet, and then running the macro which changed the dollars to euros and reduced the values to 94% of their starting values. I did not test it across multiple sheets.

 

[MunichMag]

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?