I am trying to write some code to automate the addition, change and deletion
of scenarios with Scenario Manager to avoid having to open the window and do
everything manually. I basically have an input matrix, and then the code is
supposed to either add, change or delete the scenarios. However, I am getting
a run-time error (unable to get the scenario propery of worksheet class) on
the delete section. I am a relative beginner and any help will be greatly
appreciated:

Set m = Range("f5")
Set M_1 = Range("M_1")
Set M_2 = Range("M_2")

If m.Value = "Add" Then
ActiveSheet.Scenarios.Add Name:=Array(M_1.Value),
ChangingCells:=Range("AnnMil"), _
Values:=Array(M_1.Value)
ActiveSheet.Scenarios.Add Name:=Array(M_2.Value),
ChangingCells:=Range("AnnMil"), _
Values:=Array(M_2.Value)

ElseIf m.Value = "Change" Then
ActiveSheet.Scenarios("M_1").ChangeScenario , Values:=Array(M_1.Value)
ActiveSheet.Scenarios("M_2").ChangeScenario , Values:=Array(M_2.Value)

€˜to delete a scenario
Else
ActiveSheet.Scenarios(Array(M_1.Value)).delete
ActiveSheet.Scenarios(Array(M_2.Value)).delete

End If

I am also getting the same error when I try to change a scenario. It works
fine if I assign a name to each scenario eg MileageA, but if the name is the
actual value of mileage eg 12000, then this appears to be causing the
problem. Any ideas, please?

"Graeme" wrote:

I am trying to write some code to automate the addition, change and deletion
of scenarios with Scenario Manager to avoid having to open the window and do
everything manually. I basically have an input matrix, and then the code is
supposed to either add, change or delete the scenarios. However, I am getting
a run-time error (unable to get the scenario propery of worksheet class) on
the delete section. I am a relative beginner and any help will be greatly
appreciated:

Set m = Range("f5")
Set M_1 = Range("M_1")
Set M_2 = Range("M_2")

If m.Value = "Add" Then
ActiveSheet.Scenarios.Add Name:=Array(M_1.Value),
ChangingCells:=Range("AnnMil"), _
Values:=Array(M_1.Value)
ActiveSheet.Scenarios.Add Name:=Array(M_2.Value),
ChangingCells:=Range("AnnMil"), _
Values:=Array(M_2.Value)

ElseIf m.Value = "Change" Then
ActiveSheet.Scenarios("M_1").ChangeScenario , Values:=Array(M_1.Value)
ActiveSheet.Scenarios("M_2").ChangeScenario , Values:=Array(M_2.Value)

€˜to delete a scenario
Else
ActiveSheet.Scenarios(Array(M_1.Value)).delete
ActiveSheet.Scenarios(Array(M_2.Value)).delete

End If