Replacing commas with dots in Excel script

Question

I want to replace a comma with dot and vice-versa

Public Sub VirgulaPunct()
Dim oRow As Range
Dim cell As Range
Dim i As Long, j As Long
Dim MyString As String
Dim aux As String

    Application.ScreenUpdating = False

    For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1

        For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1
            MyString = Cells(i, j).Value
            For Counter = 1 To Len(MyString)
                aux = Mid(MyString, Counter, 1)
                If aux = "." Then

                  MyString = Replace(MyString, ".", ",", 1)

                ElseIf aux = "," Then

                  MyString = Replace(MyString, ",", ".", 1)

                End If
            Next
            Cells(i, j).Value = MyString
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

Answer 1

The basic problem is that if the columns is set as general instead of text, even if we changed the "," for "." Excell automatically it will change it again for a ",".

To avoid that it is necessary to set first the column as a Text format, and then we can perform replace function:

This it works for me:

Worksheets("Name").Activate
Worksheets("Name").Columns(1).Select 'or Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"

Dim OriginalText As String
Dim CorrectedText As String

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    For i = 1 To LastRow

    OriginalText = Worksheets("Name").Cells(i, 1).Value

    CorrectedText = Replace(OriginalText, ",", ".")

    Worksheets("Name").Cells(i, 1).Value = CorrectedText

    Next i