Automating Scenarios and Storing outcomes

MitchD7

New Member
Joined
Aug 25, 2019
Messages
2
I have a fairly simple model that has 10 steps and the time & cost of each step.

IDActivityActivityTimeActivity Cost
1Step 10$0
2Step 250 $0.71
3Step 3180 $2.55
4Step 4180 $2.55
5Step 530 $0.43
6Step 65 $0.07

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>


Total: $6.30

At the end I total the cost for the 10 steps. On another sheet I have a list of changes that would affect the times for one or more of the steps.
ChangeTime (sec)Total Cost
ID110$5.50
ID23$3.25

<tbody>
</tbody>



I don't have an issue building the scenarios by hand, and I use the Change value (eg. ID1) as the scenario name. And there will be times the change affects more than 3 steps in a model. What I am struggling with is automating the running of scenario without changing the model and logging the ResultCell from the Summary report to the Total Cost cell for ID1. Otherwise I have tab upon tab of Summary Reports and have to make Total Cost equal to the cell on the applicable summary report.

I am assuming VBA.

Any ideas?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

MitchD7

New Member
Joined
Aug 25, 2019
Messages
2
And yes I just noticed my example only has 6 steps...but that number could change.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,482
Messages
5,414,797
Members
403,543
Latest member
rabdorawsh

This Week's Hot Topics

Top