The point of the code is to transform . into , It is done on csv data too big to be done manually

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub Transformer_Point_To_Virgule()
    i = 0
    b = 0

    Do While b = 0
        If UCase(ActiveCell.Value) Like "*.*" Then
            Sleep 300    
            ActiveCell.NumberFormat = "General"

            y = ActiveCell.Value    
            x = Split(y, ".")    
            Z = x(0) & "," & x(1)

            MsgBox x(0) & "   " & x(1) & Chr(10) & Z    
            ActiveCell.Value = Z

            Do While UCase(ActiveCell.Value) <> Z    
                ActiveCell.NumberFormat = "General"
                MsgBox Z
                ActiveCell.Value = Z    
            Loop
        End If

        ActiveCell.Offset(0, 1).Select
        i = i   1

        If ActiveCell = "" Then
            ActiveCell.Offset(1, -i).Select
            i = 0    
        End If

        If ActiveCell = "" Then
            b = 1
        End If    
    Loop

the problem is sometimes instead of putting: x(0) & "," & x(1) as the cell value, it only put x(0) & x(1) without the , and this give very incorrect result

I just don't understand why ='(

CodePudding user response：

Your solution looks a way too over-complicated. You just need to replace . and , by Application.DecimalSeparator to turn any cell that is numeric into a real number.

Option Explicit

Public Sub Example()
    ConvertIntoNumber ActiveSheet.Range("A1:B7")
End Sub


Public Sub ConvertIntoNumber(ByVal ConvertRange As Range)
    Dim Cell As Range
    For Each Cell In ConvertRange
        If IsNumeric(Cell.Value2) Then
            Cell.Value2 = CDbl(Replace(Cell.Value2, ".", Application.DecimalSeparator))
            Cell.Value2 = CDbl(Replace(Cell.Value2, ",", Application.DecimalSeparator))
        End If
    Next Cell
End Sub