For Next with Step Loop missing last value..sometimes

[duggie33]

Hi all...my name is Doug. I have been dabbling in VBA for a couple years and probably been a guest on here a bunch of times. I am new member today!

I have some code to fill in a bunch of cells with number incremented by a specified value. It is pretty basic stuff but I have found that it does not always work as expected. Below is the code.

Sub AddIncrementedValues()

    Dim dbl_IncrementValue As Double
    Dim dbl_StartValue As Double
    Dim dbl_EndValue As Double
    Dim i As Variant
    Dim j As Variant
    
    dbl_IncrementValue = 0.25
    dbl_StartValue = 0
    dbl_EndValue = dbl_StartValue + 100
    
    j = 0
    
    For i = dbl_StartValue To dbl_EndValue Step dbl_IncrementValue
    
        Sheet1.Range("A1").Offset(j, 0).Value = i
        j = j + 1
    
    Next i

End Sub

This code with the increment set to 0.25 works as expected. If changed to 0.2, it does not add the last value; ends at 99.8. I have also noticed that some of the values have extra digits. For instance 41.6 is actually 41.6000000000001, but 41.4 is correct at 41.4. I am not making an argument that the extra .0000000000001 is affecting the results of my calculations significantly, only that I do not understand why they are there at all.

Any insight into why this is happening or how to correct it would be greatly appreciated.

duggie33

​

<strike></strike><strike></strike>

 

[jmacleary]

Hi Doug

This is a common issue with excel (and any other computer-based system. Excel stores and calculates numbers using a binary notation, and some numbers cannot be precisely represented in this way, so VERY small discrepancies can creep in. If you set the format of your results to 15 decimal places, as you have already found you will see some small variances (e.g. 27.1999999999999000 for 27.2 and 64.2000000000004000 for 64.2). To make your loop always go to the last value, change the for loop to include a small add-on:
For i = dbl_StartValue To dbl_EndValue + 0.000000001 Step dbl_IncrementValue

<tbody>
[TD="class: xl65, align: right"][/TD]

</tbody>

 

[duggie33]

Thank you jmacleary! Your explanation and suggestion are appreciated.