Looping Solver in VBA

[cmondeau]

I'm trying to create a basic 1D cutting optimizer using Solver in VBA. However, I keep getting hung up when I try to implement Solver in a loop. I've allowed myself a few criteria to follow by, which I believe I have achieved in my code so far. Any help at all would be greatly appreciated!

Goal: Minimize the amount of stock pipe used
By Changing: Changing the amount of predetermined lengths of pipe to be cut per stock piece
Constraint 1: Each Cut length cannot exceed the maximum length of stock pipe (21')
Constraint 2: All pipes spooled must be used in the optimization template
Constraint 3: All values of pipe must be rounded to the nearest whole number
Constraint 4: If the the amount of pipes exceed the capacity of the stock pipe, additional stock must be added to the order

Currently I have 2 command buttons to complete my task, but eventually I would like to make it run with just one. I'm not super good with programming and some concepts, but have taken a couple of classes and done independent research to get myself along. Thank you for your help!

[COLOR=#222222][FONT=Verdana]Private Sub CommandButton1_Click()[/FONT][/COLOR]

Dim i As Integer


i = 1


Range("H2:L1000").ClearContents


Do While i <= Range("F2")
    Cells(i + 1, "H") = i
    i = i + 1
    
Loop


End Sub


Private Sub CommandButton2_Click()


    limit = 2
    j = 0
    Application.ScreenUpdating = True
    Do Until j = limit
        SolverReset
        SolverOk SetCell:="$M$2+j", MaxMinVal:=2, ValueOf:="0", ByChange:="$I$2+j:$L$2+j"
        solveradd cellref:="$I$2+j:$L$2+j", Relation:=4, FormulaText:="integer"
        solveradd cellref:="$M$2+j", Relation:=1, FormulaText:="$E$2"
        solveradd cellref:="$D$2", Relation:=2, FormulaText:="$C$2"
        solveradd cellref:="$D$3", Relation:=2, FormulaText:="$C$3"
        solveradd cellref:="$D$4", Relation:=2, FormulaText:="$C$4"
        solveradd cellref:="$D$5", Relation:=2, FormulaText:="$C$25"
        SolverOk SetCell:="$M$2+j", MaxMinVal:=2, ValueOf:="0", ByChange:="$I$2+j:$L$2+j"
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
        j = j + 1
    
    Loop
    
End Sub

 

[cmondeau]

Instead of looping, I think i might try creating a range of cells that I can have solver find and fill within VBA. Here is my code so far, but when I run it I get,"Error in model. Please verify that all cells and constraints are valid."
Not sure where my mistake is :/

Private Sub CommandButton1_Click()

Dim i As Integer
i = Range("$B$2").Value


SolverReset
SolverOk SetCell:="$N$5", MaxMinVal:=1, ValueOf:="0", ByChange:="$I$5:$L$&4+i"
SolverAdd CellRef:="$M$5:$M$&4+i", Relation:=1, FormulaText:="$B$1"
SolverAdd CellRef:="$D$5", Relation:=2, FormulaText:="$C$5"
SolverAdd CellRef:="$D$6", Relation:=2, FormulaText:="$C$6"
SolverAdd CellRef:="$D$7", Relation:=2, FormulaText:="$C$7"
SolverAdd CellRef:="$D$8", Relation:=2, FormulaText:="$C$8"
SolverAdd CellRef:="$I$5:$L$&4+i", Relation:=4, FormulaText:="integer"
SolverOk SetCell:="$N$5", MaxMinVal:=1, ValueOf:="0", ByChange:="$I$5:$L$&4+i"
SolverSolve


End Sub

 

[shg]

There's a workbook at https://app.box.com/shared/uhrjy318l1 that has formula-based and VBA-based methods for a 1D cut list.