Scenario Analysis and Model Output

[rpierle]

I have built a model to perform what if analysis of about 150 different scenarios with ~20 varibles that change. I want the model to cycle through my scenario, copy and paste the output to a spreadsheet, reset itself and go to the next scenario, repeating until it gets to the final scenario. Note: I have set the scenarios up as a table, not in the Scenario Manager.

I am trying to use VBA macros to automate this, but I am getting caught up the For..Next logic and having some difficulties.

Any thoughts would be appreciated.

 

[JVFriederick]

Set up array in VB as

Dim VarArray(150, 20, 2)

150 Scenarios
20 Variables
2 Variable,Row,Column (you can use 0 too)

STEP 1 Read from spreadsheet into array
=======================================
VarArray(SVar,VVar,0) = Desired Variable
VarArray(SVar,VVar,1) = Row Number
VarArray(SVar,VVar,2) = Column Number
NOTE: This depends on how and where you have stored the data in your spreadsheet.
I have not listed the code to do this, but it looks like you can do it.

STEP 2 Paste values into spreadsheet from array
===============================================
For SVar = 1 to 150

For VVar = 1 to 20
ActiveSheet.Rows(VarArray(SVar,VVar,1).Columns(VarArray(SVar,VVar,2)) = VarArray(SVar,VVar,0)
Next VVar

Calculate ' ReCalc Spreadsheet

'STEP 3 Store results somewhere else . . . . . you may only have a few to do......
ActiveSheet.Rows(1000+SVar).Columns(1) = ActiveSheet.Rows(25).Columns(12)
ActiveSheet.Rows(1000+SVar).Columns(2) = ActiveSheet.Rows(8).Columns(10)
ActiveSheet.Rows(1000+SVar).Columns(3) = ActiveSheet.Rows(66).Columns(6)
ActiveSheet.Rows(1000+SVar).Columns(4) = ActiveSheet.Rows(43).Columns(3)

Next SVar

 

[JVFriederick]

Ooops. Hit the submit during preview....
Give this a try, I think you should be able to take it from here.