MrExcel Publishing
Your One Stop for Excel Tips & Solutions

automating multiple runs of solver across worksheets


Posted by Craig Martin on September 11, 2001 3:57 PM

I have 12 worksheets in a workbook, in each one I'm running solver with a slightly different set of constraints. I tried to record a macro where I clicked into each cell, selected solver (where the constraints are already entered), hit 'solve', hit 'ok', and went on to the next sheet. However, when I run the macro, it says there's an error at the command 'SolverOK'. How can I automate the 12 runs of solver in the different worksheets?


Posted by Damon Ostrander on September 15, 2001 1:41 PM

Craig,

Chances are, the only reason why it is not working across all sheets is that the range references are not properly qualified, and therefore it is trying to run all the Solver cases on the worksheet that happens to be active when you run the macro. To properly qualify the range references, make sure that every time solver refers to a cell, for example Range("B4"), that you qualify it thus:

Worksheets("Sheet1").Range("B4")

so that the macro doesn't assume that B4 is on the active worksheet but points to the specific sheet you want it to operate on.

I hope this helps.

Damon