Recalculate automatically when using Solver

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-08-13
Is there a way to have Excel automatically recalculate a Solver formula if you just change one of the input cells without having to go in and click on "Solve?"
Question by:esu4236
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35733440
I think you would need a macro for this.

To do this

- record a macro for the solver process
The recorded macro would look something like
Sub Macro1()
End Sub

- right-click on the sheet name and click on "View code" and paste the following code lines. Replace the dots here with whatever was recorded above in place of the dots

Sub worksheet_change()
on error resume next
end sub

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 35733735
If you have already set up your Solver model, then the code can be as simple as one line in a Worksheet_Change sub.

The following code must be installed in the code pane for a worksheet. It won't work at all if installed anywhere else. You must also set a reference to the Solver add-in. You do this by checking the box for Solver in the VBA Editor using the Tools...References menu item.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Range("D2:D5,B4:B4")   'Watch these cells for changes. If any of them change, run the Solver model
If Intersect(targ, Target) Is Nothing Then Exit Sub

SolverSolve True        'Run the Solver model and automatically accept its results
End Sub

Open in new window

The preceding code works in Excel 2010. Please advise which version of Excel you use if it doesn't work in your situation.


Author Comment

ID: 35768932
Thanks so much!
LVL 50
ID: 36341193
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question