Revert a dollars to pounds conversion

user_18486751 0
Viewed 64

My US company has a division in the UK and we occasionally like to see how a proposal will look in pounds versus dollars.

This code switches a specific range in accounting format from USD to GBP.

For Each cel In lookRng
    If cel.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" Then
        cel.NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
    End If
Next cel

I tried to revert from GBP to USD using the same approach.

For Each cel In lookRng
    If cel.NumberFormat = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-" Then
        cel.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    End If
Next cel

The screen blinks with no change to the formatting.

number-formatting vba excel
1 Answers

enter image description here

Excel is altering the NumberFormat changing [$£-en-GB] to [$£-809].

Notice that the new NumberFormat contains double quotes. The easiest way to convert the format to a string is to use Replace()to double the double quotes.

?Replace(ActiveCell.NumberFormat, Chr(34), String(2, 34))
_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-

Public Const UKNumberFormat  As String = "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"
Public Const USNumberFormat  As String = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"


Sub SwitchNumberFormats(lookRng)
    Dim Cell As Range

    For Each Cell In lookRng
        If Cell.NumberFormat = UKNumberFormat Then
            Cell.NumberFormat = USNumberFormat
        ElseIf Cell.NumberFormat = USNumberFormat Then
            Cell.NumberFormat = UKNumberFormat
        End If
    Next

End Sub
user_99127140
Related