VBA keeps changing periods to commas

Question

I made a vba script that should change a specific area's values from comma-separated to dot-separated.

Here is the code:

Sub commas_to_periods()
'
' commas_to_periods Macro
' Convert all commas to Periods in a specified area that contains values before converting the file to csv
'

'
    Range("U3:AJ139").Select
    ' Selection.Replace What:=",", Replacement:=".", SearchOrder:=xlByColumns, MatchCase:=True
    ' Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
    '    SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
    '    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
    Dim V As Variant, i As Long, j As Long, T
    V = Range("U3:AJ139").Value
    howMany = 0
    Application.ScreenUpdating = False
    T = Timer
    For i = LBound(V, 1) To UBound(V, 1)
        For j = LBound(V, 2) To UBound(V, 2)
            If InStr(V(i, j), ",") > 0 Then
                V(i, j) = Replace(V(i, j), ",", ".")
                howMany = howMany + 1
            End If
        Next j
    Next i
    Range("U3:AJ139").Value = V
    msgForBox = "That took " & Timer - T & " Seconds and found: " & howMany & " commas"
    MsgBox (msgForBox)
    Application.ScreenUpdating = True
    
End Sub

I tried a couple of ways as you see but I also used that custom loop to have it count the replacements. I just can't get it to work! The message box displays that it found 100+ commas, but when the macro is done no change is made. The next time I run it, I get the exact same message in the box. Same number of commas found - no change!

If I try to replace anything else, it works just fine. Replacements are made. When I try it with the comma, it stubbornly doesn't work. Now I suppose it has something to do with my regional settings but I can't figure out a workaround. When I do a manual search and replace the commas get changed. I even recorded a macro of me making a manual search and replace and I got the same result. It seems that VBA reverts my changes to comply with my regional settings. Is there a way to go around that?

Answer 1

Note that if your regional settings are set to , as decimal points that means any numeber that contains a . will be treated as Text.

So if you want that Excel does not convert them into a number you need to ensure the number format of your cell is set to text: .NumberFormat = "@" before writing the number containing a . dot.

For example

Sub a()
    
    Selection.NumberFormat = "@"
    Selection = Replace(Selection, ",", ".")

End Sub

For multiple cells you will need to loop:

Sub b()   
    Dim Rng As Range
    Set Rng = Selection
    
    Rng.NumberFormat = "@"
    
    Dim Cell As Variant
    For Each Cell In Rng.Cells
        Cell.Value2 = Replace$(Cell.Value2, ",", ".")
    Next Cell
End Sub

because any other solution does not work as Excel thinks it is smart and turns any text back into a number.