VBA Solver loop

Question

I am using Solver with the following code:

Sub Test()

  SolverReset

  SolverOk SetCell:="$K$7", MaxMinVal:=1, ValueOf:=0, ByChange:="$I$7:$J$7", _
    Engine:=1, EngineDesc:="GRG Nonlinear"

  SolverAdd CellRef:="$G$7", Relation:=2, FormulaText:="$H$7"
  SolverAdd CellRef:="$K$7", Relation:=2, FormulaText:="$B$7"

  SolverSolve UserFinish:=False

  SolverFinish KeepFinal:=1

End Sub

I now need to put it into a loop in order to run Solver from row 7 to row 17. I coded it as per the below but it doesn't work:

Dim i As Long
For i = 7 To 17
  SolverReset

  SolverOk SetCell:="$K$" & i, MaxMinVal:=1, ValueOf:=0, ByChange:="$I$ & i:$J$ & i", _
    Engine:=1, EngineDesc:="GRG Nonlinear"

  SolverAdd CellRef:="$G$" & i, Relation:=2, FormulaText:="$H$" & i
  SolverAdd CellRef:="$K$" & i, Relation:=2, FormulaText:="$B$" & i

  SolverSolve UserFinish:=False

  SolverFinish KeepFinal:=1

Next i  

End sub

"Doesn't work" should be added to censored list... Can you explain more detailed what result are you getting - is it a compilation error, runtime exception or anything else? — GSazheniuk
– GSazheniuk, Commented Oct 7, 2015 at 16:21
It worked without the loop so it seems to be a compilation error (I am not sure about the correct syntax of the ByChange function). — fdt6243
– fdt6243, Commented Oct 7, 2015 at 16:26
so that's it, you have missed double quotes in that place - ByChange:="$I$ & i:$J$ & i" and you should use ByChange:="$I$" & i & ":$J$" & i Try and let me know if it was an issue. — GSazheniuk
– GSazheniuk, Commented Oct 7, 2015 at 16:51
That was the issue, with your syntax (ByChange:="$I$" & i & ":$J$" & i) everything works fine — fdt6243
– fdt6243, Commented Oct 7, 2015 at 17:08

shg · Accepted Answer · 2015-10-07 16:35:41Z

1

Maybe ...

Dim i             As Long

For i = 7 To 17
  SolverReset

  With Rows(i)
    SolverOk SetCell:=.Range("K1").Address, _
             MaxMinVal:=1, _
             ByChange:=.Range("I1:J1").Address, _
             Engine:=1
    SolverAdd CellRef:=.Range("G1").Address, _
              Relation:=2, _
              FormulaText:=.Range("H1").Address
    SolverAdd CellRef:=.Range("K1").Address, _
              Relation:=2, _
              FormulaText:=.Range("B1").Address
    SolverSolve UserFinish:=True
  End With
Next i

answered Oct 7, 2015 at 16:35

shg

3332 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Solver loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related