asked on

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

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rash983

ASKER

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?

Martin Liss

Which line gives that error?

Rash983

ASKER

This line >>>> Sheets(strName).Activate
End Sub

Martin Liss

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

Martin Liss

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.

Rash983

ASKER

Thank you very much for your help. Yes, it does but with the this issue the Sensitivity report is never generated.

Rash983

ASKER

Can I email you the file to look at the problem closely?

Martin Liss

You can send me an EE Message and attach the file.