Automating Solver in loop
 

Hello:
I am trying to use the SOlverOK module ina loop ina macro and I am no
able to reset the cosntraint set. Code I am using is below. PLEAS
HELP!!
thanks
msuryexcel
****
For j = 2 To numdates - k1
rj = rk + j
ri = rj - 500
riold = ri - 1
Range(col3 & rj).Select
SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0"
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverDelete CellRef:="$AC$" & riold, Relation:=2
FormulaText:="1"
SolverAdd CellRef:="$AC$" & ri, Relation:=2, FormulaText:="1"
SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0"
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverDelete CellRef:="$E$" & riold & ":$AA$" & riold
Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$" & ri & ":$AA$" & ri, Relation:=3
FormulaText:="0"""

SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0"
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverSolve

Next j
***

--
msuryexce
-----------------------------------------------------------------------
msuryexcel's Profile: http://www.excelforum.com/member.php...fo&userid=2816
View this thread: http://www.excelforum.com/showthread.php?threadid=47684

Automating Solver in loop
 

I am trying to use the SOlverOK module in a loop ina macro and I am not
able to reset the cosntraint set. Code I am using is below. PLEASE...

Hello. From my experience, I find it very difficult to keep track of which
constraints need to be modified.
The expression has to be exact for the deletion to work. I also find it
hard to work with strings.
I like to Reset everything when using Solver in a loop. I can't follow the
exact problem, but see if there is anything here that can help. I assume
the problems are on Rows 1-10.
I threw in a few techniques that I use. HTH. :)

Sub Demo()
'= = = = = = = = = =
'// Dana DeLouis
'= = = = = = = = = =
Dim R As Long '(R)ow
Dim Rng As String
Const EqualTo As Long = 2
Const GreaterThanOrEqualTo As Long = 3
Dim Result As Long

For R = 1 To 10
SolverReset

Rng = Range(Cells(R, 5), Cells(R, 27)).Address
SolverOk SetCell:=Cells(R, 3), MaxMinVal:=2, ByChange:=Rng

SolverAdd Cells(R, 29), EqualTo, 1
SolverAdd Rng, GreaterThanOrEqualTo, 0

Result = SolverSolve(True)
If Result = 3 Then
MsgBox "Solver Error on Row: " & R
End
End If
Next R
End Sub

--
Dana DeLouis
Win XP & Office 2003


"msuryexcel" wrote
in message ...

Hello:
I am trying to use the SOlverOK module ina loop ina macro and I am not
able to reset the cosntraint set. Code I am using is below. PLEASE
HELP!!
thanks
msuryexcel
****
For j = 2 To numdates - k1
rj = rk + j
ri = rj - 500
riold = ri - 1
Range(col3 & rj).Select
SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0",
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverDelete CellRef:="$AC$" & riold, Relation:=2,
FormulaText:="1"
SolverAdd CellRef:="$AC$" & ri, Relation:=2, FormulaText:="1"
SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0",
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverDelete CellRef:="$E$" & riold & ":$AA$" & riold,
Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$" & ri & ":$AA$" & ri, Relation:=3,
FormulaText:="0"""

SolverOk SetCell:=col3 & rj, MaxMinVal:=2, ValueOf:="0",
ByChange:= _
"$E$" & ri & ":$AA$" & ri
SolverSolve

Next j
****


--
msuryexcel
------------------------------------------------------------------------
msuryexcel's Profile:
http://www.excelforum.com/member.php...o&userid=28161
View this thread: http://www.excelforum.com/showthread...hreadid=476847

Automating Solver in loop
 

Hi Dana:
Thanks for your response. I agree with the SolverReset idea. I also
found another post by Tushar Mehta on 9/9/05 with a slightly different
approach. I tried to copy that code which seems to work EXCEPT that
any constraint such as "x = 1" seems to disappear when the loop is
processed. I ran the macro for one iterationa dn then used "EXC" to
stop the macro and check what is in the Solver and I see that the
constraints like " x0" are there but "x =1" type cosntraints are
missing.


Of Course I can create a new variable (y = x-1) and then add "y0" -
but this should be unnecessary.

Any suggestions?
Thanks for your help
msuryexcel


--
msuryexcel
------------------------------------------------------------------------
msuryexcel's Profile: http://www.excelforum.com/member.php...o&userid=28161
View this thread: http://www.excelforum.com/showthread...hreadid=476847