Do until with a defined function

Question

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

Michał Turczyn · Accepted Answer · 2020-03-10T14:24:05.300

0

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

edited Mar 10 '20 at 14:24

answered Mar 10 '20 at 11:53

Michał Turczyn

32,028
14
47
69

I see you can replace the DO UNtil with the for loop. I still cannot understand how i can define the sum of the values in column E. d2value needs to be BIGGER THAN the SUM of the values from Cells(2,5) until Cells(2+i,5). For example, D2= 500;E2=100;E3=300;E4=300, then, i=3 – Antonio Mar 10 '20 at 14:20
@Antonio If answer helped you, you should accept it by checking green check mark on the left – Michał Turczyn Mar 12 '20 at 12:45

score 0 · Answer 2 · edited Nov 10 '22 at 21:52

Following the first problem, I tried to add another loop to go through all the values for the StockOh in column D. Basically after, it find the i for the first number in D2, I want to repeat the iteration for the values in D3, D4, ... I think the problem is somewhere with the use of the "Exit loop" but will see.

THIS IS AN ADDITIONAL QUESTION, LET ME KNOW IF I SHOULD OPEN A NEW THREAD.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' get last row in E column
Dim lastRow As Long
lastRow = Cells(Rows.Count, 5).End(xlUp).Row

' get last row in D column
Dim lastRow_stock As Long
lastRow_stock = Cells(Rows.Count, 4).End(xlUp).Row

Dim i, stock, y As Integer

' define sum
Dim sumInE As Double
sumInE = 0

'Define counter
y = 0

'loop over D column
For stock = 2 + y To lastRow_stock

'Loop over E column
For i = 2 To lastRow

'Get value in column D
Dim d2value As Double
d2value = Cells(stock, 4).Value
  sumInE = sumInE + Cells(i, 5).Value
    If sumInE > d2value Then
Cells(stock, 6).Value = i

Exit For
End If

MsgBox (y)
y = y + 1
                   
Next
Next stock
End Sub

Do until with a defined function

2 Answers2