how to loop excel solver on a range of cells???
how to loop excel solver on a range of cells???
(OP)
i'm trying to minimize a target cell with two constraints, with formulas in different columns. the solver works fine when i do it manually, but when i try to intergrate a macro, it screws up. it ignores one of my constraints. so the result is wrong.
my second problem is that i can't figure out how to loop the macro so that it repeats the same minimization, but to a different row. I have about 40 rows to compute.
This is the vba i have:
it's the first contraint (g78=1) that is ignored by the solver using the macro.
thanks
my second problem is that i can't figure out how to loop the macro so that it repeats the same minimization, but to a different row. I have about 40 rows to compute.
This is the vba i have:
CODE
SolverReset
SolverOk SetCell:="$I$78", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$78:$F$78"
SolverAdd CellRef:="$G$78", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$A$78", Relation:=2, FormulaText:="$H$78"
SolverOk SetCell:="$I$78", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$78:$F$78"
SolverSolve
End Sub
SolverOk SetCell:="$I$78", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$78:$F$78"
SolverAdd CellRef:="$G$78", Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$A$78", Relation:=2, FormulaText:="$H$78"
SolverOk SetCell:="$I$78", MaxMinVal:=2, ValueOf:="0", ByChange:="$B$78:$F$78"
SolverSolve
End Sub
thanks





RE: how to loop excel solver on a range of cells???
You can easily set up a loop and give the arguments to the solver by string concatenation (the '&' operator).
The SolverSolve True argument allows you to bypass the finish dialog.
CODE
For r = 78 To 118
SolverReset
SolverAdd CellRef:="$G$" & r, Relation:=2, FormulaText:="1"
SolverAdd CellRef:="$A$" & r, Relation:=2, FormulaText:="$H$" & r
With ActiveSheet
.Names.Add Name:="solver_opt", RefersTo:=.Name & "!$I$" & r, Visible:=False 'SetCell
.Names.Add Name:="solver_adj", RefersTo:=.Name & "!$B$" & r & ":$F$" & r, Visible:=False 'ByChange
End With
SolverOk SetCell:="$I$" & r, MaxMinVal:=2, ValueOf:="0", ByChange:="$B$" & r & ":$F$" & r
SolverSolve True
Next r
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: how to loop excel solver on a range of cells???
I'm not sure i understand the code, but if it works, that's all i need.
thanks a lot