Recalculate automatically when using Solver

Posted on 2011-05-10
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
    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 80

    Accepted Solution

    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

    Thanks so much!
    LVL 50

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now