VBA to switch Currency Formats (USD, GBP, EUR, etc)

C

cubsfan05

New Member
Joined
Jun 10, 2013
Messages
32
Office Version
  1. 365
Platform
  1. Windows
My overall goal with the code below is to replace all $ formats in a workbook with £ or € as selected by the user.

However the code seems to get stuck on the line " Application.ReplaceFormat.NumberFormat = ReplaceNew " which returns null.
As far as I can tell, the only reason for this is that the GBP currency/accounting format is not a valid format. It is however, the exact result produced when recording the macro and jives with the format on the cell format (ctrl+1) screen.

Is there some trick to denoting a non-dollar currency in VBA?
Any ideas how to correct the code below?

Code: 
Sub ChangeCurrency()' ChangeCurrency Macro
' Programmed by Lucas Weiss on 10/31/16


    Dim NewCurrency, OldCurrency As String
    Dim find1 As String, find2 As String, find3 As String, find4 As String, find5 As String
    Dim replace1 As String, replace2 As String, replace3 As String, replace4 As String, replace5 As String
    Dim ws As Worksheet
    
    
    NewCurrency = Range("currency_flag").Value
    OldCurrency = "USD"
    
    'Sheets.Select
        
    Select Case OldCurrency
        Case "USD"
            find1 = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"  'accounting w/o decimals
            find2 = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"  'accounting w/ decimals
            find3 = "$#,##0.00_);($#,##0.00)" 'currency w decimals
            find4 = "$#,##0_);($#,##0)"  'currency w/o decimals
            find5 = ",""$"  'in text formulas
        Case Else
            MsgBox "Existing currency must be changed manually.", 0, "Error: Change Currency Manually"
    End Select
    
    Select Case NewCurrency
        Case "GBP"
            replace1 = "_-[$£-809]* #,##0_-;-[$£-809]* #,##0_-;_-[$£-809]* ""-""_-;_-@_-"  'accounting w/o decimals
            replace2 = "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"  'accounting w/ decimals
            replace3 = "[$£-809]#,##0.00;-[$£-809]#,##0.00"  'currency w decimals
            replace4 = "[$£-809]#,##0"  'currency w/o decimals
            replace5 = ",""£"  'in text formulas
        Case Else
            MsgBox "Selected currency must be changed manually.", 0, "Error:Select a Different Currency"
    End Select
    
    For Each ws In ActiveWorkbook.Sheets
        Call FindReplaceCurrency(find1, replace1)
        Call FindReplaceCurrency(find2, replace2)
        Call FindReplaceCurrency(find3, replace3)
        Call FindReplaceCurrency(find4, replace4)
        Cells.Replace What:=find5, Replacement:=replace5, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
    Next ws
    
    
End Sub


'--------------------------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------------------------
Sub FindReplaceCurrency(FindOld As String, ReplaceNew As String)
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = FindOld
    Application.ReplaceFormat.Clear
[COLOR=#ff0000][B]    Application.ReplaceFormat.NumberFormat = ReplaceNew[/B][/COLOR]
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
cubsfan05 said:
My overall goal with the code below is to replace all $ formats in a workbook with £ or € as selected by the user.

However the code seems to get stuck on the line " Application.ReplaceFormat.NumberFormat = ReplaceNew " which returns null.
As far as I can tell, the only reason for this is that the GBP currency/accounting format is not a valid format. It is however, the exact result produced when recording the macro and jives with the format on the cell format (ctrl+1) screen.

Is there some trick to denoting a non-dollar currency in VBA?
Any ideas how to correct the code below?

Code: 
Sub ChangeCurrency()' ChangeCurrency Macro
' Programmed by Lucas Weiss on 10/31/16


    Dim NewCurrency, OldCurrency As String
    Dim find1 As String, find2 As String, find3 As String, find4 As String, find5 As String
    Dim replace1 As String, replace2 As String, replace3 As String, replace4 As String, replace5 As String
    Dim ws As Worksheet
    
    
    NewCurrency = Range("currency_flag").Value
    OldCurrency = "USD"
    
    'Sheets.Select
        
    Select Case OldCurrency
        Case "USD"
            find1 = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"  'accounting w/o decimals
            find2 = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"  'accounting w/ decimals
            find3 = "$#,##0.00_);($#,##0.00)" 'currency w decimals
            find4 = "$#,##0_);($#,##0)"  'currency w/o decimals
            find5 = ",""$"  'in text formulas
        Case Else
            MsgBox "Existing currency must be changed manually.", 0, "Error: Change Currency Manually"
    End Select
    
    Select Case NewCurrency
        Case "GBP"
            replace1 = "_-[$£-809]* #,##0_-;-[$£-809]* #,##0_-;_-[$£-809]* ""-""_-;_-@_-"  'accounting w/o decimals
            replace2 = "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"  'accounting w/ decimals
            replace3 = "[$£-809]#,##0.00;-[$£-809]#,##0.00"  'currency w decimals
            replace4 = "[$£-809]#,##0"  'currency w/o decimals
            replace5 = ",""£"  'in text formulas
        Case Else
            MsgBox "Selected currency must be changed manually.", 0, "Error:Select a Different Currency"
    End Select
    
    For Each ws In ActiveWorkbook.Sheets
        Call FindReplaceCurrency(find1, replace1)
        Call FindReplaceCurrency(find2, replace2)
        Call FindReplaceCurrency(find3, replace3)
        Call FindReplaceCurrency(find4, replace4)
        Cells.Replace What:=find5, Replacement:=replace5, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True
    Next ws
    
    
End Sub


'--------------------------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------------------------
Sub FindReplaceCurrency(FindOld As String, ReplaceNew As String)
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = FindOld
    Application.ReplaceFormat.Clear
[COLOR=#ff0000][B]    Application.ReplaceFormat.NumberFormat = ReplaceNew[/B][/COLOR]
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub
Click to expand...
ithink that there was a small error in the code
Rich (BB code): 
private Sub FindReplaceCurrency(FindOld As String, ReplaceNew As String)
    With Application
        With .FindFormat
            .Clear
            .NumberFormat = FindOld
        End With
Rem switched the clear function around
        With .ReplaceFormat
            .NumberFormat = ReplaceNew
            .Clear
        End With
    End With
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub
 
Upvote 0
Based on my read of your code, you moved the clear function to after setting the replaceformat which does not solve the problem of the replaceformat returning null.
to prove this i copied your code into my model and it produced the same error
 
Upvote 0
What about removing the clear statement altogether?
 
Upvote 0
No luck, I believe the issue has something to do with the actual formatting string which appears to produce a null result despite being what is produced when recording the macro.

The below code is what produces the null result when combined with the previously bolded code (Application.ReplaceFormat.NumberFormat = ReplaceNew)
Code: 
replace1 = "_-[$£-809]* #,##0_-;-[$£-809]* #,##0_-;_-[$£-809]* ""-""_-;_-@_-"  'accounting w/o decimals
 
Upvote 0
cubsfan05 said:
No luck, I believe the issue has something to do with the actual formatting string which appears to produce a null result despite being what is produced when recording the macro.

The below code is what produces the null result when combined with the previously bolded code (Application.ReplaceFormat.NumberFormat = ReplaceNew)
Code: 
replace1 = "_-[$£-809]* #,##0_-;-[$£-809]* #,##0_-;_-[$£-809]* ""-""_-;_-@_-"  'accounting w/o decimals
Click to expand...

Have you tried Excel.Range.FormulaLocal? It apparently changes the formats to the local currency symbol instead.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Replace Formats Across ALL SHEETS in Workbook
Replies
3
Views
2K
Legacy 393636
L

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top