Excel Solver for large data set (varying constants)?

Because Excel Solver can only have one cell for 'Target Cell', it's unclear how I can automate Solver to analyze a large data set?

A good example of how I'm using Excel's Solver is given here: http://www.vertex42.com/ExcelArticles/ex... by Example #2, 2 eqn's and 2 unknowns.

This example shows for any given values of constants "r1,r2,r3", you'll get your "angle" solutions. However, this example only shows how to setup Solver for one set of constants. What if I have (by analogy to this example), 3 columns of data with "r1,r2,r3", how do I use Solver to find solutions down column 4? Indeed, I can setup Solver to analyze each row by copying cells, but I'd like to analyze a ~100 hundred rows with one click. Can I somehow automate solver to do this? I'm hoping the solution is simple, but it's not intuitive.

Regards,
John

Your link doesn't work, so I can't see what you are trying to do. However, if you need to run the Solver many times and want to automate it then that is no problem. The Solver is fully programmable in VBA.

To automate the Solver in VBA you first need to set a reference to Solver.xla or Solver.xlam (Excel 2003 or 2007) in your VBA project. Open the VBA editor. Add a new module and open it. Then go to Tools --> References. If Solver isn't listed, then use the Browse button to find it in the /Library/Solver folder of your Office folder.

Once you've done that you are ready to go. Search for Solver in the VBA help and you will find all of the functions (SolverReset, SolverOk, SolverSolve, etc). You can run it through whatever looping structure fits your needs, resetting the target cell, changing cells, and even the constraints on each pass.

Tim
http://feeds.feedburner.com/ExcelBlog