[SOLVED] Do until with a defined function

Do until with a defined function

I am trying to learn some VBA for my job. Unfortunately, i don't have the time to follow a course so i hope to learn the logic by executing some tasks.

I have the StockOH in one cell (D2) and i want to know how many days of sales i can cover. The days of sales are recorded in the column E (starting from cell E2 - E1 is the title).

I am using a "Do Until" loop that should end when the sum of Sales in E2:E(i) is higher than D2.

Sub Loop_until()
Dim i As Integer
i = 1
Dim x
Set x = Range("G2")
Range("G2") = "=SUM(R[2]C[5]:R[2+i]C[5]"
Do Until Range(D2).Value < x
    Range(F2).Value = i
     i = i + 1
Loop
End Sub

Solution

Just loop over E column, on each row check if value is greater than D2 value.

Something like this:

' get value ine cell D2
Dim d2value As Double
d2value = Cells(2, 4).Value
' get last row in E column
Dim lastRow As Long
lastRow = Cells(Rows.Count, 5).End(xlUp).Row
' define sum
Dim sumInE As Double
sumInE = 0
' loop over E column
Dim i As Long
For i = 2 to lastRow
  sumInE = sumInE + Cells(i, 5).Value
  If sumInE > d2value Then
    Exit For
  End If
Next

MsgBox i