Automate the Solver add-in

0

You helped with the Solver add-in solution.  Now, I am trying to have my spreadsheet automatically use Solver and its resulting answer in the cell so when I change my cash flow assumptions the Solver automatically calculates the new answer without having to manually run Solver each time.  

Answer
Discuss

Discussion

You need to select the answer for the previous IRR question. Go to the one that worked for you and click the Select Answer button under it and then Yes to confirm. If you go back to that post, which you can get to by clicking the My Questions link at the top of the forum, you should see a red warning message telling you to do this.
don (rep: 1382) Jul 20, '16 at 1:19 pm
Done 
mrandall (rep: 4) Jul 20, '16 at 1:23 pm
Ok great. I know this forum is not "traditional" so if you have any tips for getting new users to select the answers, let me know! 
don (rep: 1382) Jul 20, '16 at 2:21 pm
Add to Discussion

Answers

1
Selected Answer

To get this to work you need to do a few things.

First, make sure the Solver Add-in is installed on the computer where you will use this. Follow this tutorial to install the Solver Add-in (the only thing to note in that tutorial is that for Step 5, you select Solver Add-in)

Second, make sure the VBA environment can handle Solver:

  1. Hit Alt + F11 to go to the VBA Editor window (where you install macros).
  2. Go to the Tools menu > References > put a check mark next to Solver (it should be near the top) > click OK.

Third, install the macros:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed_cells As Range

Set Changed_cells = Range("D8:D12")

If Not Application.Intersect(Changed_cells, Range(Target.Address)) Is Nothing Then

    Call solver_run

End If

End Sub

This one goes into the worksheet where you want it to run. In the VBA Editor window, double-click the worksheet where your data is, Sheet2 in your example, and then paste the macro in there.

Change D8:D12 to the range of cells that you want to cause the Solver to run when they are changed.

Sub solver_run()

SolverReset
SolverOK _
    SetCell:=Range("F17"), _
    MaxMinVal:=3, _
    ValueOf:=Range("F15").Value, _
    ByChange:=Range("F12")
SolverSolve UserFinish:=True

Application.Calculation = xlCalculationAutomatic

End Sub

Put this in a regular module.

F17 is the cell that you want to equal a certain value.

F15 is the value you want F17 to equal.

F12 is the cell that is changed to make F17 equal F15.

If you need more help installing the macro, read this tutorial: Install a Macro in Excel

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login