SOLVER in VBA
Is the single quote significant in "Dim R As Long 'Row"?
Hi. In this context, characters typed after the single quote are just
remarks.
I was just using the letters R & C to represent (R)ow and (C)olumn.
As a side note, if you could rearrange your worksheet, and have cells
reference the external workbook, then you may be able to combine a few
statements. Perhaps something like this:
' Remark: 1 means Less Than or Equal to
SolverAdd "A1:A10", 1, "B1:B10"
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003
"Cornelia" wrote in message
...
Thank you Dana - you don't want to know how long I've been messing with
this!
And your code seems to be so much simpler/shorter. A couple of questions,
though, as I am a novice:
Is the single quote significant in "Dim R As Long 'Row"?? I've never used
this type of Dim statement before.
You have been a big help!
"Dana DeLouis" wrote:
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
|