Using Solver Thru VBA

  • Thread starter Thread starter Mallick
  • Start date Start date
M

Mallick

Hi there
I was using VBA to automate repeated use of Solver. My requirements are that
solver is called twice for two models in different worksheets and then, the
process is repeated till a satisfactory answer reached. I wrote following
lines to do part of my problem:

SolverReset
SolverOk SetCell:=Worksheets(1).Range($F$6:$F$10), MaxMinVal:=2,
ByChange:=Worksheets(1).Range("Y")
SolverAdd CellRef:=Worksheets(1).Range(Z), Relation:=3, FormulaText:="U"
SolverOPtions AssumeLinear:=True, AssumeNonneg:=True
SolverSolve(UserFinish:=True)
SolverFinish KeepFinal:=1

The problem I am encountering is that the Solver changes the cell
"$F$6:$F$10" only on the worksheet which is active despite explicitly
defining the changeable cells on worksheet1. If worksheet 3 is active, it
will change its cells "$F$6:$F$10". Since, I am running two solvers on
seperate worksheets, both solvers change cells on the worksheet which is
active.

Can any one help, please Thanks
 
Both the Target cell and the Adjustabl;e cell have to be on the active
worksheet.
 
SolverOk SetCell:=Worksheets(1).Range($F$6:$F$10)

Hello. Your reference for Target doesn't meet the two requirements.
ie Single Cell, and Active Sheet

From Help:
SetCell Optional Variant. Refers to a single cell on the active worksheet.


As a side note...
SolverSolve(UserFinish:=True)
SolverFinish KeepFinal:=1
Click to expand...

I noticed your use of () in the SolverSolve line.
"Usually" one tests Solver's results via that method:
ie
Results = SolverSolve(True) 'Hopefully, 0,1, or 2

= = = = =
HTH
Dana DeLouis
 
Back
Top