CODEWITHSUNDEEP
excelvba
Mojimi
Mojimi

Reputation: 3161

Keep excel from auto formating string to number in VBA

I have this code that is extracting values from another sheet. These values are TEXT, but after extracting Excel converts them to number no matter what, even with the columns set to format as text, even using 

Format( value , "#")

The problem is that they are big numbers and it is changing any digits above the 15 count to 0's, I need the numbers as they are.

They are not cut in the reference column(j,3), so it is changing in the process

Dim i, col1, col2 As Integer
i = 2
col1 = 4
col2 = 5
    For j = 2 To 2205
        If Cells(j, 3).Value <> "" Then
            If Len(Cells(j, 3)) = 20 Then
                    Cells(i, col1).Value = Format(Cells(j, 3).Value, "#")
                    Cells(i, col2).Value = Cells(j, 3).Row
                    Cells(j, 3).Value = ""
                    i = i + 1
            End If
        End If
    Next j
    i = 2
    col1 = col1 + 2
    col2 = col2 + 2

Upvotes: 0

Views: 2626

Answers (1)

DHL-JDParker
DHL-JDParker

Reputation: 46

Excel has a limitation of 15 digits of precision, so numbers over 15 digits on either side of the decimal will not be stored at the desired precision. Have you tried setting the cell format before you insert the value? Use the line Cells(i, col1).NumberFormat = "@" before setting the value.

    Dim i, col1, col2 As Integer
    i = 2
    col1 = 4
    col2 = 5
        For j = 2 To 2205
            If Cells(j, 3).Value <> "" Then
                If Len(Cells(j, 3)) = 20 Then
                        Cells(i, col1).NumberFormat = "@"
                        Cells(i, col1).Value = Format(Cells(j, 3).Value, "#")
                        Cells(i, col2).Value = Cells(j, 3).Row
                        Cells(j, 3).Value = ""
                        i = i + 1
                End If
            End If
        Next j
        i = 2
        col1 = col1 + 2
        col2 = col2 + 2

Upvotes: 1

Related Questions