SOLVER in VBA
This one doesn't work ...
SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2,
Hi. Beginning with Excel 97, Solver switched from R1C1 notation to A1
notation. So, it no longer like the R1C1 part...
One easy option is to just use the address property.
Note that you do not need "Value Of" when solving for a Min. problem.
Dim R As Long 'Row
Dim C As Long 'Column
'etc...
MyTarget = Cells(R * 20 + 7, C).Address
MyChange = Range(Cells(R * 20, C), Cells(R * 20 + 4, C)).Address
SolverReset
SolverOk MyTarget, 2, , MyChange
' Other possible options...
Results = SolverSolve(True) 'Disregard popup
Select Case Results
Case 0, 1, 2
'Solver found a solution
Case Else
'Problem...
End Select
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003
"Cornelia" wrote in message
...
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
|