CODEWITHSUNDEEP
vbaexcel
lawstud
lawstud

Reputation: 185

Changing decimal separator in VBA

I've written simple code in VBA (and seen questions here and here and none of these solutions work).

            Dim toString As String
            toString = cell.Value & "_"
               If (InStr(toString, ",")) Then
                 toString = Replace(toString, ",", ".")
                 toString = Trim(toString)
                 cell.Value = " " + Left(toString, (Len(toString) - 1))               
               End If

Unfortunately, instead of string with dot separator, excel gives me double with comma in cell.Value. What curious is, when I exchange this whitespace with "_", it converts f. ex. 12,3 into _12.3. How can I fix it?

P.S. I add "_" at the end to ensure that toString will remain String.

Upvotes: 3

Views: 2041

Answers (1)

TheGuyOverThere
TheGuyOverThere

Reputation: 130

I've had this issue before. You need to change the formatting of the cell before you write to it.

Application.Workbooks("Book1").Sheets("Sheet1").Range("A1:A100").NumberFormat = "@"

After that line runs you can simply write to the column like this:

Cells(1,1).Value = "12.3"

Excel will keep the string formatting and not convert it to a double.

Hope this helps.

Upvotes: 2

Related Questions