• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1578
  • Last Modified:

Recalculate automatically when using Solver

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?"
1 Solution
Saqib Husain, SyedEngineerCommented:
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

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.

esu4236Author Commented:
Thanks so much!
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now