VBA Code fill cells with increment of 0.1

Question

I want to fill column A with values starting from 0 up to the value set in range("b1") with 0.1 increments. I have the code below but when I run it, it doesn't stop on the value that I set on range("b1"). Help?

Dim x As Double

Do

     x = x + 0.1

     Range("A" & Rows.count).End(xlUp).Offset(1).Value = x

Loop Until x = Range("b1").Value

Possible duplicate of [Why Are Floating Point Numbers Inaccurate?](https://stackoverflow.com/questions/21895756/why-are-floating-point-numbers-inaccurate) — Tomalak, Feb 11 '18 at 16:50
Never use floating point numbers for equality comparisons. (Using them in *inequality* comparisons is fine though. Try rewriting your code to use `<=` or `>=` instead.) — Tomalak, Feb 11 '18 at 16:51

score 0 · Answer 1 · answered Feb 11 '18 at 16:55

Try converting the numbers during the comparison.

Private Sub this()
    Dim i As Long, y As Double, x As Double
    x = CDbl(ThisWorkbook.Sheets("Sheet1").Range("b1").Value)
    Debug.Print ; x
    y = 0
    i = 1
    For i = 1 To 999
        ThisWorkbook.Sheets("Sheet1").Range("a" & i).Value = y
        y = y + 0.01
        Debug.Print ; y
        If CLng(y) = CLng(x) Then
            Exit For
        End If
    Next i
End Sub

OR

Private Sub this()
    Dim i As Long, y As Double, x As Double
    x = CDbl(ThisWorkbook.Sheets("Sheet1").Range("b1").Value)
    Debug.Print ; x
    y = 0
    i = 1
    For i = 1 To 999
        ThisWorkbook.Sheets("Sheet1").Range("a" & i).Value = y
        y = y + 0.01
        Debug.Print ; y
        If CStr(y) = CStr(x) Then
            Exit For
        End If
    Next i
End Sub

score 0 · Answer 2 · answered Feb 11 '18 at 17:05

0

Using as double can be problematic for EQUALS (long decimal) numbers. If you change your loop to be Loop Until x >= Range("b1").Value you should get desirable results.

answered Feb 11 '18 at 17:05

pgSystemTester

8,979
2
23
49

score 0 · Accepted Answer · answered Feb 11 '18 at 21:26

you could "transpose" the whole problem to integer numbers and then divide them by 10 to get back decimals

Option Explicit

Sub main()
    Dim iLoop As Long
    For iLoop = 1 To Range("b1").Value * 10 + 1
         Range("A" & iLoop).Value = (iLoop - 1) * 0.1
    Next
End Sub

VBA Code fill cells with increment of 0.1

3 Answers3