Scenario enhanced.

  • Thread starter Thread starter Jack Mallinckrodt
  • Start date Start date
J

Jack Mallinckrodt

I have a complex system modeled on a spreadsheet. n identifiable cells are
input parameters, m identifiable cells are output results.

I want to plot some of the outputs as functions of some of the inputs. For
this I need to make a table of which the first n columns are manually input
parameters, and the next m the spreadsheet results. I need something like
an enhanced "scenario" macro or function to, for each row, take the first
n cells (columns), copy those values into the corresponding input parameter
cells, recalculate the spreadsheet, and copy the results cells into the
next m columns of the table. Then proceed to next row.

Is there such a thing? Would it be a feasible macro?

Thanks,

Jack
 
Hi Jack

Try the following:

Public Sub Test()
For i = 0 To 10
Range("A1:A5").Offset(0, i).Copy
Range("Z1").PasteSpecial xlPasteValues

Calculate

Range("AA1:AA5").Copy
Range("BA1").Offset(0, i).PasteSpecial xlPasteValues
Next
End Sub

This will need a bit of modifying to suit your layout. What it does is to
take a set of ten scenarios set out in columns A to K. These are copied one
at a time to Column Z. The spreadsheet recalculates. Then the output results
are assumed to be in Column AA and these are copied one set at a time to
columns BA to BK.

I have assumed that the data for each scenario occupies the first 5 rows.
Just change 5 to whatever no. of rows you need. Similarly for the results
section.

HTH

BTW can someone help me please. The copy and pastespecial is quite
cumbersome. Is there a simple way of copying just values using copy with a
destination? For example, the following does not work, and I am not clear
why it doesn't.

Range("A1:A5").Offset(0, i).value.Copy destination:= Range("Z1")

Thanks

Geoff
 
Hi Jack

Try the following:

Public Sub Test()
For i = 0 To 10
Range("A1:A5").Offset(0, i).Copy
Range("Z1").PasteSpecial xlPasteValues

Calculate

Range("AA1:AA5").Copy
Range("BA1").Offset(0, i).PasteSpecial xlPasteValues
Next
End Sub

This will need a bit of modifying to suit your layout. What it does is to
take a set of ten scenarios set out in columns A to K. These are copied one
at a time to Column Z. The spreadsheet recalculates. Then the output results
are assumed to be in Column AA and these are copied one set at a time to
columns BA to BK.

I have assumed that the data for each scenario occupies the first 5 rows.
Just change 5 to whatever no. of rows you need. Similarly for the results
section.

HTH

BTW can someone help me please. The copy and pastespecial is quite
cumbersome. Is there a simple way of copying just values using copy with a
destination? For example, the following does not work, and I am not clear
why it doesn't.

Range("A1:A5").Offset(0, i).value.Copy destination:= Range("Z1")

Thanks

Geoff
 
Back
Top