| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SOLVER in VBA
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SOLVER in VBA
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SOLVER in VBA
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. Cornelia wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Solver | Excel Discussion (Misc queries) | |||
| Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
| Solver | Excel Discussion (Misc queries) | |||
| Resetting Solver Manually to Fix Solver Bug | Excel Programming | |||
| solver | Excel Programming | |||
Hybrid Mode
