L
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.
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.