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:

SolverOk SetCell:=Worksheets(1).Range($F$6:$F$10), MaxMinVal:=2,
SolverAdd CellRef:=Worksheets(1).Range(Z), Relation:=3, FormulaText:="U"
SolverOPtions AssumeLinear:=True, AssumeNonneg:=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

Can any one help, please Thanks

Gary''s Student

Both the Target cell and the Adjustabl;e cell have to be on the active

Dana DeLouis

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...
SolverFinish KeepFinal:=1

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

= = = = =
Dana DeLouis

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