0

This is what ia m trying to do: I have the StockOH in column (D) and i want to know how many days of sales i can cover with each amount. So, for example, if the stockOH value in D2 is more than the sum of value E2:E5 but less than Sum(E2:E6), this means i can cover 4 days of sales.

I am using a Do until funciton nested to a for loop. I am not sure why the first resutl is correct while the next ones are not

enter image description here

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 Dim y, x, Ssellout As Double

 'loop thorugh the stock values in column D

 For y = 2 To 5

 'Get each amount of stock
  Ssellout = Cells(y, 5).Value

 x = y

 'FInd the breakout point when sum of sellout is higher than stockOH
 Do Until Cells(y, 4).Value < Ssellout

 'Add 1 to the counter in case condition is not valid
  x = x + 1

 'REport value found as a breakeven
 Cells(y, 6).Value = x

'Add another day of sales to the sum of sales
  Ssellout = Ssellout + Cells(x, 5).Value
 Loop

 MsgBox (y)

 Next
End Sub
Antonio
  • 35
  • 3

1 Answers1

0

I finally managed with FOr Loop and Do While functions.

Here is the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim x, y As Integer

Dim sellout, stock As Double

For y = 0 To 4

'Define counter for sum IMPORTANT it should be inside the for loop
sellout = 0
x = 2

Do While Cells(2 + y, 4).Value > sellout

sellout = sellout + Cells(x + y, 5).Value
Cells(2 + y, 6).Value = x - 2
x = x + 1
Loop

Next


End Sub
Antonio
  • 35
  • 3