Automating Solver in loop

[msuryexcel]

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
****

[Dana DeLouis]

> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[msuryexcel]

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 " x>0" are there but "x =1" type cosntraints are missing.


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

Any suggestions?
Thanks for your help
msuryexcel