Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Creating a macro to run Solver

222 views

Skip to first unread message

John

unread,

Jun 4, 2001, 12:31:02 PM6/4/01

I have created a spreadsheet in Excel 97 that utilises the solver
function and I want to create a macro that runs the solver function
which I can then assign to a button.

I need to set K3 as my target cell
By changing H3

Now this is the bit that's causing me the problem.

I need to set the "Value Of" field to the contents of Cell C5

I havent been able to figure out a way of doing this as Solver insists
that a number is inserted into the field, not a formula. I've got the
solver add-in set up on Excel and I've spent all day playing with the
solver.xla file which contains a number of macro's but can't get it to
work.

I'd be so grateful if someone could write me the macro that would run
the solver command by picking up the contents of the 3 cells mentioned
above.

Regards

John

Jay Petrulis

unread,

Jun 5, 2001, 2:01:08 PM6/5/01

Hi John,

This should do the trick. Make sure you have the Solver add-in
referenced in your project (in VBA, Tools->References->Solver.xls
checked)

In your procedure, use the following:
----------------------------------------
SolverOk SetCell:="$K$3", MaxMinVal:=3, ValueOf:=Range("C5").Value,
ByChange:="$H$3"
SolverSolve
----------------------------------------

There is likely a more elegant solution, but this worked for me.

Thanks,
Jay

avn...@hotmail.com (John) wrote in message news:<869c4b9c.01060...@posting.google.com>...