×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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:

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

RE: how to loop excel solver on a range of cells???

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.

RE: how to loop excel solver on a range of cells???

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources