LEN() returning wrong value in VBA
<p>I have this simple data set:</p>

<pre><code>230
16000
230
230000
230000
230000
16000000
230000
230000
</code></pre>

<p>all i want is to get the length of each cell but when i write this code:</p>

<pre><code>Sub LengthOfCell()
Dim c As Long
Dim result As Integer

c = ActiveCell.Value
result = Len(c)
Debug.Print (result)


End Sub
</code></pre>

<p>it gives me 2 for the first cell (230) when it should be 3 and 4 for any number having more than 3 digits. dont know what i am doing wrong. tis is just for proof of concept for a larger SUB:</p>

<pre><code>Public Sub SortMyData()

'approach: convert line to string and concatenate to that as it's a lot less picky than Excel's formats, then replace cell value with the new string.
'          Excel will then define the string type as either Percentage or Scientific depending on the magnitude.
Dim i As Integer
Dim N_Values As Integer

N_Values = Cells(Rows.Count, 2).End(xlUp).Row
            'Range("B6", Range("B5").End(xlDown)).Count

For i = 6 To N_Values 'iteration loop from 6 (first row of value) to N_Values (last filled row)
    Cells(i, 3).NumberFormat = "0"

    If Cells(i, 2).NumberFormat &lt;&gt; "0.0%" Then
        Cells(i, 2).NumberFormat = "0.0%"
        Cells(i, 2).Value = Cells(i, 2).Value / 100

        ElseIf Len(Cells(i, 3).Value &gt; 3) Then
            Cells(i, 3).Value = Cells(i, 3).Value / 1000

        ElseIf Cells(i, 3).Value = Null Then
            Cells(i, 3).Value = 0

    Else
        Cells(i, 2).Value
        Cells(i, 3).Value
    End If
       ' If Len(Cells(i, 3) &gt; 3) Then
           ' Cells(i, 3).Value = Cells(i, 3).Value / 1000
           ' ElseIf Cells(i, 3).Value = Null Then
                'Cells(1, 3).Value = 0
       ' Else
           ' Debug.Print
       ' End If
Next

 End Sub
</code></pre>