Create a macro that automates solver when a cell value is changed

[anthony_91]

Hi, I'm relatively new to Excel and haven't done any VBA coding of any kind. Essentially, what I'm trying to do is automate Solver so that when cell A1 or A2 is changed, my worksheet will automatically run Solver (and keep the solver solution). So when I go into solver, my parameters are as follows

Set Objective: $W$16
To: Min
By Changing Variable Cells: $W$7
Subject to the constraints: $W$13 = $H$7

Any assistance would be appreciated.

Thanks

[Alf]

For a setup like that you need two macros. The first one is a "Worksheet_Change Event" macro.

Please Login or Register  to view this content.

This will fire whenever a value is entered in either cell A1 or A2. Copy this macro, right click on the sheet tab where you have your solver model and click "View Code". Paste the "Private Sub ..." macro in the windows that opens.

The second macro you need is the solver macro.

Please Login or Register  to view this content.

This macro must be pasted to a module in Visual Basic. Go to "Developer" tab, click "Visual Basic" click "Insert" and select "Module" and paste the "Sub Sol_Run" code in the new windows that opens.

Before you can run solver by using a macro you must set a reference to Solver in Visual Basic. In the Visual Basic window click "Tools" then select "References". Look for Solver and tick box in front of it to set the reference. If solver not found use browse and get Solver.xlam

Usually found "\Microsoft Office\Office 14\Library\Solver\Solver.xlam"

Alf

[anthony_91]

Thanks Alf. That was really helpful

For a setup like that you need two macros. The first one is a "Worksheet_Change Event" macro.

Please Login or Register  to view this content.

This will fire whenever a value is entered in either cell A1 or A2. Copy this macro, right click on the sheet tab where you have your solver model and click "View Code". Paste the "Private Sub ..." macro in the windows that opens.

The second macro you need is the solver macro.

Please Login or Register  to view this content.

This macro must be pasted to a module in Visual Basic. Go to "Developer" tab, click "Visual Basic" click "Insert" and select "Module" and paste the "Sub Sol_Run" code in the new windows that opens.

Before you can run solver by using a macro you must set a reference to Solver in Visual Basic. In the Visual Basic window click "Tools" then select "References". Look for Solver and tick box in front of it to set the reference. If solver not found use browse and get Solver.xlam

Usually found "\Microsoft Office\Office 14\Library\Solver\Solver.xlam"

Alf

[Alf]

Glad to be of help.

If this solves your problem could you please mark your thread "Solved" and if you like click on the small star (bottom left) to give a rating to my efforts.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

Alf

Ps In this forum we don't quote a full posting, just a line or some words if necessary to emphasis a point.