VBA: activecell.value = variable does not work

Question

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 ='(

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `ActiveCell` and `Select` is a bad practice. — Pᴇʜ, Oct 24 '22 at 14:52
Why so complicated and not just replacing `.` by `,` using the `replace` method? What is your `Application.DecimalSeperator`? — Pᴇʜ, Oct 24 '22 at 14:55

score 1 · Accepted Answer · answered Oct 24 '22 at 15:16

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

VBA: activecell.value = variable does not work

1 Answers1