Excel Solver with VB

[Ly]

I am trying to solve a bunch of equations with similar structure (say,
min $M$i by change of $B$i:$E$i with subject to a few constraints, i
runs from 1 to 100). I found this topic
http://groups.google.com/group/micr...er+excel+repeat+macro&rnum=3#7abece79402a0da4
very helpful. However, when I run my macro, all 100 results were
exactly the same and very different from what I would have gotten had I
manually gone through the Solver GUI.

My initial thought was that maybe the constraints were not cleared
after each time. But SolverReset should take care of this problem, I
suppose. I still have a few hundred more equations to run. So I would
very appreciate it if anyone could tell me how to fix the problem and
what I did wrong.

Here is my macro:

Sub EF()

For j = 0 To 3
SolverReset
Set firstrange = Range("M38").Offset(j, 0)
Set secondrange = Range("B38:E38").Offset(j, 0)
Set cons1 = Range("B38").Offset(j, 0)
Set cons2 = Range("C38").Offset(j, 0)
Set cons3 = Range("D38").Offset(j, 0)
Set cons4 = Range("E38").Offset(j, 0)
Set cons5 = Range("J38").Offset(j, 0)
Set cons6 = Range("L38").Offset(j, 0)
Set targetER = Range("A38").Offset(j, 0)

SolverOk _
SetCell:=firstrange, _
MaxMinVal:=2, _
ValueOf:=0, _
ByChange:=secondrange

SolverAdd _
CellRef:=cons1, _
Relation:=3, _
FormulaText:="0"

SolverAdd _
CellRef:=cons2, _
Relation:=3, _
FormulaText:="0"

SolverAdd _
CellRef:=cons3, _
Relation:=3, _
FormulaText:="0"

SolverAdd _
CellRef:=cons4, _
Relation:=3, _
FormulaText:="0"

SolverAdd _
CellRef:=cons5, _
Relation:=2, _
FormulaText:="1"

SolverAdd _
CellRef:=cons6, _
Relation:=3, _
FormulaText:=targetER


SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

Next j

End Sub

Thank you very much.

 

[Dana DeLouis]

Hi. Just a quick guess that TargetEr is not getting added.
See if this works. You need to give the constraint the "Address", and not
the cells Value.

SolverAdd _
CellRef:=cons6, _
Relation:=3, _
FormulaText:=targetER.Address

Just some quick ideas for your consideration.

Dim R As Long ' (R)ow
For R = 38 To 40
SolverReset

No need for "Value of" in a Minimization problem. (It's ignored anyway)

SolverOk _
SetCell:=Cells(R, "M"), _
MaxMinVal:=2, _
ByChange:=Cells(R, "B").Resize(1, 4).Address


Consider adding 4 constraints in one line:

SolverAdd _
CellRef:=Cells(R, "B").Resize(1, 4), _
Relation:=3, _
FormulaText:="0"

Anyway, just some thoughts.

 

[Ly]

Thank you soooo much.
It works beautifully, and I also made a silly mistake by specifying a
wrong relationship.

 

[Dana DeLouis]

Glad it was an easy fix. :>)
Thanks for the feedback.