SOLVER in VBA

C

Cornelia

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
 
D

Dana DeLouis

This one doesn't work ...
SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2,
Click to expand...

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 said:
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
Click to expand...
 
C

Cornelia

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 said:
This one doesn't work ...
SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2,
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
S

SteveM

Often you do not have to write a statement for each constraint
statement. The easiest way to work with solver code is to name your
data, target cell and solution cells as ranges, then turn on the macro
recorder, reset the formulation, then reformulate your model. Solver
will convert your named range constraints into a single statement.
Once your formulation is finished, turn off the recorder. Go to the VB
module that contains the solver code and add any additional statements
you may need, e.g. SolverSolve(true). If you mess up, just repeat the
macro record process.

One other thing, an Integer Tolerance of 5% is pretty loose. Start at
5% and if the solution time is reasonable, you can reduce that which
may yield a potentially superior solution.

Steve M.

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 said:
This one doesn't work ...
SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2,
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dana DeLouis

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 said:
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 said:
This one doesn't work ...
SolverReset
SolverOk SetCell:="R" & j * 20 + 7 & "C" & i, MaxMinVal:=2,
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
Solver 0
Solver iteration in VBA macro 2
SOLVER Reset command does not work in 2007Excel why???? Two Questi 0
Regarding excel and VBA 1
Macro for solver on certain worksheets 2
Macro Skip the Constraint (Solver) 0
Set solver constraint not working in VB 6

Top