Solver in Excel (loop)

[youssieboud]

Hello everyone,

I'm trying to use the solver in Excel multiple times (in a loop), but I can't figure out how to do it.

I want the F19 cell as changing value and I want to set N15 to 0.10. First, I want to do this for a volume of 100 m3 (N3=100). Using the solver for this procedure works excellent. But now, I want to use the solver for increasing values of the volume, from V=100 up until V=12.000 for example, while using loops. And I also want to capture those iterated values for each step.

Can someone help me out?

 

[dcardno]

Warning - this is air code...

Sub Solve()

Dim i As Integer
Dim rgVol As Range
Dim rgOutput As Range

Set rgVol = Range("N3")
Set rgOutput = Range("J20")
For i = 1 To 12 Step 1

    rgVol = i * 100
    SolverOk SetCell:="$N$15", maxminval:=3, valueof:=0.1, _
    bychange:="$F$19", Engine:=1, EngineDesc:="GRG NonLinear"
    solversolve
    rgOutput.Offset(i, 0) = rgVol
    rgOutput.Offset(i, 1) = Range("$F$19").Value

Next i

End Sub

I would usually use defined names instead of cell references, and you probably want to include captions for the output values (and perhaps record more of the values), but that should get you headed in the right direction. If you need more granularity, change the number of steps ("i") and multiplier in the above.