Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro for solver on certain worksheets
Have a workbook that I want to create a macro use solver on multiple sheets.
1st Sheet is a summary (solver must ignore) 2nd and 3rd Sheet have the same layout but different values. I want solver to optimise each sheet individually. For both the solver will use the same cells e.g. target cell is the same, and cells that I want changed etc. I want the macro to do this automatically as my workbook is likely to gain more sheets. I am a novice at visual basic. The macro will be activated from the summary sheet via a button (I can create the button).. This the solver code for both sheet 2 and sheet 3 is shown below, that I have recorded: Sub Macro4() SolverReset SolverOk SetCell:="$AI$64", MaxMinVal:=2, ValueOf:="0", ByChange:= _ "$S$15:$S$16,$W$11:$AA$11" SolverAdd CellRef:="$W$11:$AA$11", Relation:=1, FormulaText:="120" SolverAdd CellRef:="$S$15:$S$16", Relation:=3, FormulaText:="0.5" SolverOk SetCell:="$AI$64", MaxMinVal:=2, ValueOf:="0", ByChange:= _ "$S$15:$S$16,$W$11:$AA$11" SolverSolve UserFinish:=True End Sub Is possible for me to use this code and create a macro to achieve my goal? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for solver on certain worksheets
Yes. Before the code you have now insert Sheets("Sheet2").Activate or
Sheets("Sheet2").Select. Then repeat the code but replace "Sheet2" with "Sheet3". If you plan to expand to several worksheets, then embed the Solver code in a loop. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for solver on certain worksheets
On Thursday, February 16, 2012 8:17:09 AM UTC-5, merjet wrote:
Yes. Before the code you have now insert Sheets("Sheet2").Activate or Sheets("Sheet2").Select. Then repeat the code but replace "Sheet2" with "Sheet3". If you plan to expand to several worksheets, then embed the Solver code in a loop. Can you please demonstrate the code to create a loop for multiple sheets? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
solver macro | Excel Programming | |||
solver macros in protected worksheets | Excel Programming | |||
Solver Macro | Excel Programming | |||
Solver Macro | Excel Programming | |||
Solver activates other worksheets - why? | Excel Programming |