Using Solver Thru VBA
 

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

Using Solver Thru VBA
 

Both the Target cell and the Adjustabl;e cell have to be on the active
worksheet.
--
Gary''s Student - gsnu200908

Using Solver Thru VBA
 

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

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



On 10/25/09 11:49 AM, Mallick wrote:
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