VBA macro to run the sensitivity analysis report

I've been trying to write a macro to run the sensitivity analysis report by clicking a command button instead of going to data> solver> solve> sensitivity report> ok (worksheet "optimize" ). I created a similar command button to run solver but I need another button to get the sensitivity report. Could you please help me with that? please find attached Excel file.
VBA.xlsx
Rash983Asked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
I assume you have the Solver add-in installed and that you have a Reference to it set in the VBE. If so then add the code shown here to your button.

Private Sub RunSolver_Click()
Dim ws As Worksheet
Dim strName As String
    SolverOk SetCell:="$B$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$2:$D$8", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1, ReportArray:=Array(2)
    Worksheets(Worksheets.Count).Activate
    
    ' Find the new report and show it.
    For Each ws In Worksheets
        If Left$(ws.Name, 11) = "Sensitivity" Then
            If ws.Name > strName Then
                strName = ws.Name
            End If
        End If
    Next
    Sheets(strName).Activate
End Sub

Open in new window

0
 
Rash983Author Commented:
I am trying to use the macro to do the same thing in Microsoft Excel 97-2003 Worksheet (.xls) but I get this message  "run time error 9 subscript out of range" What should I do?
0
 
Martin LissOlder than dirtCommented:
Which line gives that error?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rash983Author Commented:
This line >>>>   Sheets(strName).Activate
End Sub
0
 
Martin LissOlder than dirtCommented:
I don't use those versions of Excel but for that line try either

Worksheets((strName).Activate

or

Worksheets((strName).Select

Or you could just comment out lines 10 to 18. The report will still be created but you won't taken to the resulting sheet.

In any case I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
 
Martin LissOlder than dirtCommented:
I just had another (and probably better) thought. In Excel Excel 97-2003, does the Sensitivity reports still start with the word "Sensitivity"? Line 12 expects that it does and if not strName will never get a value and you;ll get the "subscript out of range" error.
0
 
Rash983Author Commented:
Thank you very much for your help. Yes, it does but with the this issue the Sensitivity report is never generated.
0
 
Rash983Author Commented:
Can I email you the file to look at the problem closely?
0
 
Martin LissOlder than dirtCommented:
You can send me an EE Message and attach the file.
0
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.

All Courses

From novice to tech pro — start learning today.