Calculate Total needed to Order

[Eddie Estrella]

Good Afternoon,
I having been trying to create a macro that would take the On Hand balance (Col. O) and subtract itself from the months of demand (Cols. B:N) while leaving zeros where it is used and updating the balance each time until it reaches the last month. For example:
On hand = 100, Demand for Jul is 50. So it would subtract 50 from the On Hand making the demand 0 and reduce itself to 50 and continue on to the last month. The On Hand balance can be a negative number as that will tell me the quantity I need to order.

I can't think of anything to do this and I'm not sure it's even possible. I've attached a sample file of what I am looking at.

Thank you!

 

[p45cal]

specifically for your sample file:

Sub blah()
For Each cll In Range("o2:o3").Cells
' cll.Select
  onhand = cll.Value
  For ofset = -12 To -1
    With cll.Offset(, ofset)
    ' .Select
      demand = .Value
      .Value = demand - Application.Median(demand, onhand, 0)
      onhand = onhand - demand
    End With
  Next ofset
  cll.Value = onhand
Next cll
End Sub

 

[jindon]

For example:
On hand = 100, Demand for Jul is 50. So it would subtract 50 from the On Hand making the demand 0 and reduce itself to 50 and continue on to the last month. The On Hand balance can be a negative number as that will tell me the quantity I need to order.

Isn't it just

Sub test()
    Dim r As Range
    For Each r In Range("o2", Range("o" & Rows.Count).End(xlUp))
        r.Value = r.Value - Application.Sum(r.EntireRow.Range("c1:n1"))
        r.EntireRow.Range("c1:n1").Value = 0
    Next
End Sub

?

 

[Eddie Estrella]

Wow, Thank you both so much. I'm new to VBA and the direction I was going was off.