Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Solver Thru VBA
Both the Target cell and the Adjustabl;e cell have to be on the active
worksheet. -- Gary''s Student - gsnu200908 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver VBA - Defining Solver Options | Excel Programming | |||
referencing Solver.xlam vs. Solver.xla - 2007 vs. 2003 | Excel Programming | |||
How to replace Excel solver with some free solver codes in Excel V | Excel Programming | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Resetting Solver Manually to Fix Solver Bug | Excel Programming |