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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Automate the Solver add-in
0
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: 1989) 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: 1989) Jul 20, '16 at 2:21 pm
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:
- Hit Alt + F11 to go to the VBA Editor window (where you install macros).
- 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 SubThis 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