For Next with Step Loop missing last value..sometimes

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
365
Platform
Windows
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.

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>
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
942
Office Version
2007
Platform
Windows
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>
</tbody>
 
Last edited:

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
365
Platform
Windows
Thank you jmacleary! Your explanation and suggestion are appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,029
Messages
5,466,116
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top