I am trying to use SOLVER to minimize cost based on 5 varying elements. I
can get it to work when I record a macro based on a single set of data. But
when I create a loop to run through multiple blocks of data, it appears to be
working (I think I have the syntax correct - it definitely takes its time and
loops through my data)...BUT it doesn't optimize. When I do the single case,
SOLVER generates a pop-up in which I choose "continue" and then another
pop-up in which I choose to overwrite and keep the optimized solution. I
don't get these pop-ups in my loop. (But I know it is looping, as I've put
in status bar to track progress). I'd like to just accept the optimized
solution and have it overwrite without having the answer the question because
my file is large (40 blocks of data organized in rows and 36 columns across
representing 36 months of data).

Any suggestions would be greatly appreciated as I am a novice trying to
learn to program in VBA and have limited experience with SOLVER as well.

I've posted code below. This one works:

SolverOk SetCell:="$D$27", MaxMinVal:=2, ValueOf:="0",
ByChange:="$D$20:$D$24"

SolverAdd CellRef:="$D$20", Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$F$4"
SolverAdd CellRef:="$D$20", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$G$4"

SolverAdd CellRef:="$D$21", Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$I$4"
SolverAdd CellRef:="$D$21", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$J$4"

SolverAdd CellRef:="$D$22", Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$L$4"
SolverAdd CellRef:="$D$22", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$M$4"

SolverAdd CellRef:="$D$23", Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$O$4"
SolverAdd CellRef:="$D$23", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$P$4"

SolverAdd CellRef:="$D$24", Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$R$4"
SolverAdd CellRef:="$D$24", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$S$4"

SolverAdd CellRef:="$D$30", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$U$4"
SolverAdd CellRef:="$D$31", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$V$4"
SolverAdd CellRef:="$D$32", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$W$4"
SolverAdd CellRef:="$D$33", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$X$4"
SolverAdd CellRef:="$D$34", Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!$Y$4"

SolverAdd CellRef:="$D$25", Relation:=2, FormulaText:="1"

SolverSolve


This one doesn't work (loops thru i and j; CoRow is a defined integer):

SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2, ValueOf:="0",
ByChange:="R" & j * 20 & "C" & i & ":R" & j * 20 + 4 & "C" & i
SolverAdd CellRef:="R" & j * 20 & "C" & i, Relation:=3, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C6"
SolverAdd CellRef:="R" & j * 20 & "C" & i, Relation:=1, FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C7"

SolverAdd CellRef:="R" & j * 20 + 1 & "C" & i, Relation:=3,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C9"
SolverAdd CellRef:="R" & j * 20 + 1 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C10"

SolverAdd CellRef:="R" & j * 20 + 2 & "C" & i, Relation:=3,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C12"
SolverAdd CellRef:="R" & j * 20 + 2 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C13"

SolverAdd CellRef:="R" & j * 20 + 3 & "C" & i, Relation:=3,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C15"
SolverAdd CellRef:="R" & j * 20 + 3 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C16"

SolverAdd CellRef:="R" & j * 20 + 4 & "C" & i, Relation:=3,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C18"
SolverAdd CellRef:="R" & j * 20 + 4 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C19"

SolverAdd CellRef:="R" & j * 20 + 10 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C21"
SolverAdd CellRef:="R" & j * 20 + 11 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C22"
SolverAdd CellRef:="R" & j * 20 + 12 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C23"
SolverAdd CellRef:="R" & j * 20 + 13 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C24"
SolverAdd CellRef:="R" & j * 20 + 14 & "C" & i, Relation:=1,
FormulaText:= _
"[ETH_MONSD_REV.xls]FeedFlex!R" & CoRow & "C25"

SolverAdd CellRef:="R" & j * 20 + 5 & "C" & i, Relation:=2,
FormulaText:="1"

SolverOptions MaxTime:=600, Iterations:=100, Precision:=0.000001,
AssumeLinear _
:=True, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=False
SolverSolve UserFinish:=True

Next i
Next j