Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to loop excel solver on a range of cells??? 1

Status
Not open for further replies.

shanvan

Specifier/Regulator
Nov 8, 2006
4
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:
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
it's the first contraint (g78=1) that is ignored by the solver using the macro.
thanks
 
Replies continue below

Recommended for you

It looks like the Solver is looking for a defined name in the activesheet, but then uses a defined name in the workbook (the elusive topic of local and workbook names). I have fixed it by adding two lines to add the names to the activesheet, as well as passing them as an argument to SolverOK.
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:
Dim r As Long
    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.
 
i tried it and with a few changes, it works perfectly... it still didnt like the constraint = 1... so i put 1 in a column and refered it to it.
I'm not sure i understand the code, but if it works, that's all i need.
thanks a lot
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor