none
More interesting behaviour between European and American versions of Excel RRS feed

  • Question

  • Our company has Excel installed in both America and Europe, 2010 version.

    The following macro works in America to find cells with dollar signs and turn them to euro signs.  However, it doesn't work in Europe.  Why?  How does one fix this?

            For Each c In Worksheets("X").Range("A1:Z100").Cells

                If c.NumberFormat = "$#,##0.00" Then

                c.NumberFormat = "[$€-2] #,##0.00"

                ElseIf c.NumberFormat = "$#,##0" Then

                c.NumberFormat = "[$€-2] #,##0"

                ElseIf c.NumberFormat = "$#,##0_);($#,##0)" Then

                c.NumberFormat = "[$€-2] #,##0"

                ElseIf c.NumberFormat = "$#,##0.00_);($#,##0.00)" Then

                c.NumberFormat = "[$€-2] #,##0.00"

                ElseIf c.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" Then

                c.NumberFormat = "_([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* ""-""??_);_(@_)"

                End If

            Next

    Tuesday, November 10, 2015 4:15 PM

Answers

  • First of all try the code below and find out which cells to not work as required and what is the existing Numberformat.

    IMHO Select Case is far better programming then all of the If and ElseIf. Much easier to follow what the code is doing.

    Note: You might know this but just in case you don't. In the VBA editor use Ctrl and G to open the Immediate Window and view the Debug.Print output. (With a lot of output you might lose the first outputs because there is a limit to how much can be output to the Immediate Window.)

    Sub test()

        Dim c As Range

        'For Each c In Worksheets("X").Range("A1:Z100").Cells
        For Each c In Worksheets("X").Range("A1:A20").Cells     'This range used for testing only
            Select Case c.NumberFormat
                Case "$#,##0.00"
                   c.NumberFormat = "[$€-2] #,##0.00"
                  
                Case "$#,##0"
                   c.NumberFormat = "[$€-2] #,##0"
                  
                Case "$#,##0_);($#,##0)"
                   c.NumberFormat = "[$€-2] #,##0"
                  
                Case "$#,##0.00_);($#,##0.00)"
                   c.NumberFormat = "[$€-2] #,##0.00"
                  
                Case "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                   c.NumberFormat = "_([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* ""-""??_);_(@_)"
                  
                Case Else
                   Debug.Print "Range " & c.Address(0, 0) & _
                        " no match found for " & Chr(34) & c.NumberFormat & Chr(34)
                  
            End Select
        Next c
    End Sub


    Regards, OssieMac


    Wednesday, November 11, 2015 1:15 AM
  • Maybe this will work for you

    Dim sNF As String
    Dim c As Range
        For Each c In Selection
            sNF = c.NumberFormat
            sNF = Replace(sNF, "[$€-2]", "$")
            If InStr(1, sNF, "$") Then
                c.NumberFormat = Replace(sNF, "$", "[$€-2]")
            End If
        Next
    the first Replace is to avoid replacing any existing [$€-2] with [[$€-2]€-2]

    Sunday, November 15, 2015 4:08 PM
    Moderator

All replies

  • First of all try the code below and find out which cells to not work as required and what is the existing Numberformat.

    IMHO Select Case is far better programming then all of the If and ElseIf. Much easier to follow what the code is doing.

    Note: You might know this but just in case you don't. In the VBA editor use Ctrl and G to open the Immediate Window and view the Debug.Print output. (With a lot of output you might lose the first outputs because there is a limit to how much can be output to the Immediate Window.)

    Sub test()

        Dim c As Range

        'For Each c In Worksheets("X").Range("A1:Z100").Cells
        For Each c In Worksheets("X").Range("A1:A20").Cells     'This range used for testing only
            Select Case c.NumberFormat
                Case "$#,##0.00"
                   c.NumberFormat = "[$€-2] #,##0.00"
                  
                Case "$#,##0"
                   c.NumberFormat = "[$€-2] #,##0"
                  
                Case "$#,##0_);($#,##0)"
                   c.NumberFormat = "[$€-2] #,##0"
                  
                Case "$#,##0.00_);($#,##0.00)"
                   c.NumberFormat = "[$€-2] #,##0.00"
                  
                Case "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
                   c.NumberFormat = "_([$€-2]* #,##0.00_);_([$€-2]* (#,##0.00);_([$€-2]* ""-""??_);_(@_)"
                  
                Case Else
                   Debug.Print "Range " & c.Address(0, 0) & _
                        " no match found for " & Chr(34) & c.NumberFormat & Chr(34)
                  
            End Select
        Next c
    End Sub


    Regards, OssieMac


    Wednesday, November 11, 2015 1:15 AM
  • OK I am checking on this, thanks.
    Wednesday, November 11, 2015 9:18 PM
  • Maybe this will work for you

    Dim sNF As String
    Dim c As Range
        For Each c In Selection
            sNF = c.NumberFormat
            sNF = Replace(sNF, "[$€-2]", "$")
            If InStr(1, sNF, "$") Then
                c.NumberFormat = Replace(sNF, "$", "[$€-2]")
            End If
        Next
    the first Replace is to avoid replacing any existing [$€-2] with [[$€-2]€-2]

    Sunday, November 15, 2015 4:08 PM
    Moderator