Solver Using VBA

[Swoootie]

I am producing a template Excel based optamisation tool using Solver. I want to roll this out to other members of the team but I would like there to be as little manual intervention as possible. Ideally, where there are a number of possible solutions to a problem I would like VBA to select and output a solution rather than promting the user or producing numerous pop ups.
Is there a way to do this.

Thanks

 

[Andrew Poulsom]

I think I would need to see some sample data (with formulas) before I can help any further.

 

[Swoootie]

I'll prepare a template to send over to you, as I cannot send any sample data over in its current format.
Thanks for your help.

 

[Swoootie]

Hi Andrew,

Saving the spreadsheet as an older Excel file (i.e. as an .xls and not .xlsm file) has somehow resolved the issue of the pop-ups. However in saving this file as a Excel 97-2003 workbook, the functionality of my sheet is compromised and Solver no longer provides me with a solution like it did in the newer .xlsx or .xlsm versions.

Are you aware of problems with using Solver in VBA pre 2007?

I'm really desperate for a solution to this, as the Solver code that I set up previously (that I sent earlier in this thread) worked fine but now no longer performs as expected.

Thanks

 

[Swoootie]

It's like the optamisation is not happening. The pop up that Solver did not find a solution appears very quickly and I can see from the results that there has been no iterative trial and error taking place as I know there is a definite solution (I've worked one out by hand). </SPAN>

 

[Andrew Poulsom]

As I said before I would need to see some sample data (with formulas) before I can help any further.