Simultaneous while decrement (yep I searched)

[RandyJseatac]

We have about 200 rows of data. Each row is a day.
Column A values come from other formulas on that row.
Column A values range from 0 to 20
Each new entry in column A contributes "1" to the total in column B for each row until it (A) has decremented to 0.
Each entry in column A should decline by one on each of the rows that follow (each passing day)
Yep, the A's overlap sometimes.

An example may illustrate better:
Column B is the correct answer (manually). My question is how do I program that?
Column B is a running total of how many Simultaneous A's are currently "active" on that particular day (row)

Thanks for any help! It's been a head-scratcher for days.

3 1
3 2
3 3
0 2
0 1
0 0
9 1
0 1
2* 2
0 2
4 2 <- on this row, the 4 adds one, the 2* gets to zero, and the 9 has 5 rows left before it is zero
0 2
1 3

Note: the results do not need to be on the same rows as the data. Bottom line is that I'm looking for the maximum simultaneous A's. -- "3" in this case

 

[Peter_SSs]

I must admit I am not particularly strong with Solver, especially in combination with vba. I also don't have a good grasp of what is happening in your sheet. However, it seems like you may be trying to run the code when the Solver produces new values but you are also trying to use the new values in the Solver. Is that the case?

When the error occurs and you debug, what are the values of the following variables?
i
a(i,1)

What is the link between G5:K5 and AA12:AAx? The formulas you quoted earlier don't seem to make any link.

 

[RandyJseatac]

i=1 and a(i,1)=Error 2015
The error pops up before solver starts running in earnest... during the "Setting Up" phase as indicated in the status bar.
You are correct... solver runs fine if I don't include AC8 in the Solver goal or constraints. It may be that Solver can't handle VBA code between the input and goal. I've submitted a request to Frontline Systems (makers of solver) to confirm this.

Thanks for your help so far Peter. This is a great improvement! It's midnight in this part of the world, I will check in tomorrow.

 

[craig.penny]

I'm not recommending you go with this method but I just wanted to answer your question from earlier. If you wanted this to work when you calculate on that sheet you need to open the worksheet module specific to that sheet and paste in this code:

Private Sub Worksheet_Calculate()
Const SetSize As Integer = 13
Dim Mat(1 To SetSize, 1 To SetSize) As Integer
Dim i As Integer, j As Integer, k As Integer
For i = 1 To SetSize
      For j = 1 To WorksheetFunction.Min(Cells(i, 1), SetSize - i + 1)
            Mat(i + j - 1, i) = 1
      Next j
Next i
For i = 1 To SetSize
      k = 0
      For j = 1 To SetSize
            If Mat(i, j) > 0 Then k = k + 1
      Next j
      Cells(i, 2) = k
Next i
End Sub

So, not the workbook module but the worksheet module where this should run.

 

[RandyJseatac]

Craig- that's an interesting change. It runs continuously which is fine, but the result never decreases. It either increases or stays the same. (Your algorithm works perfectly one time, running it manually per your previous post)


Also:

Trying to run it along with Solver, stops while Solver is "Setting up..." and produces the Run-time error 1004: Unable to get the Min property of the WorksheetFunction class.

Debug highlights For j = 1 to WorksheetFunction.Min(Cells...
and J = 0

I guess I should have included "Solver" in this thread title. Darn thing.

 

[craig.penny]

Well, two things I should have mentioned. That code would work if you had it in the workbook module but it would have run on every sheet which would be annoying or even destructive with regards to the data in your other sheets. The other thing is that Peter's methods are what you should probably pursue since I was just taking a swing at it. I don't know much about solver and how it interacts with code. Plus, he's got that cool MVP thing!