I have a model that produces multi-year financial output for a product.
The model has an input to change the product under review. I need to
create summary reports based on this financial output for a number of
products, which gets a little tricky as far as I can tell. Originally
I was using a two-way data tables with one axis as the product input
values and the other axis as the values I need for the output, i.e. Y1
Revenue, Y2 Revenue, etc. I was accomplishing this using choose
function for the output axis. However, this involved some manual
copying and pasting and performed somewhat slowly, so I thought VBA
would do the trick. With the help of some folks in this forum, I've
created the following code, which appears to work, but is *extremely*
slow -- even slower than the data table solution. Any suggestions on
how to improve the speed? Here's the code:


Sub ScenOut()
Dim rngNames As Range, rngScenInput As Range
Dim rng1 As Range
Dim cell As Range, cell1 As Range
Dim nm As Name
Dim c As Integer, r As Integer
Dim iMaxScen As Integer
Dim wks As Worksheet

Set wks = Worksheets("Output")
wks.Range("A3").Activate

Set rngNames = Range(Application.Names!OutputNames)
Set rngScenInput = Range(Application.Names("Product.Input"))

c = 1
iMaxScen = 10

Do While c <= iMaxScen
rngScenInput.Value = c
For Each cell In rngNames
r = 0
Set rng1 = Range(cell.Value)
For Each cell1 In rng1
r = r + 1
wks.Cells(r, c).Value = cell1.Value
Next cell1
Next cell
Loop
End Sub

For starters, you might try adding:

Application.ScreenUpdating = False 'to the top of your code
Application.ScreenUpdating = True 'to the bottom of your code


Regards,
Paul

"sloth" wrote in message
oups.com...
I have a model that produces multi-year financial output for a product.
The model has an input to change the product under review. I need to
create summary reports based on this financial output for a number of
products, which gets a little tricky as far as I can tell. Originally
I was using a two-way data tables with one axis as the product input
values and the other axis as the values I need for the output, i.e. Y1
Revenue, Y2 Revenue, etc. I was accomplishing this using choose
function for the output axis. However, this involved some manual
copying and pasting and performed somewhat slowly, so I thought VBA
would do the trick. With the help of some folks in this forum, I've
created the following code, which appears to work, but is *extremely*
slow -- even slower than the data table solution. Any suggestions on
how to improve the speed? Here's the code:


Sub ScenOut()
Dim rngNames As Range, rngScenInput As Range
Dim rng1 As Range
Dim cell As Range, cell1 As Range
Dim nm As Name
Dim c As Integer, r As Integer
Dim iMaxScen As Integer
Dim wks As Worksheet

Set wks = Worksheets("Output")
wks.Range("A3").Activate

Set rngNames = Range(Application.Names!OutputNames)
Set rngScenInput = Range(Application.Names("Product.Input"))

c = 1
iMaxScen = 10

Do While c <= iMaxScen
rngScenInput.Value = c
For Each cell In rngNames
r = 0
Set rng1 = Range(cell.Value)
For Each cell1 In rng1
r = r + 1
wks.Cells(r, c).Value = cell1.Value
Next cell1
Next cell
Loop
End Sub

I would expect it to run forever, since you say

Do While c <= iMaxScen

and you never change the value of "c" inside the loop.

Why are you using addressing like this:
Set rngNames = Range(Application.Names!OutputNames)
Set rngScenInput = Range(Application.Names("Product.Input"))


Set rngNames = Range("OutputNames")
Set rngScenInput = Range("Product.Input")

would be more direct.

--
Regards,
Tom Ogilvy


"sloth" wrote:

I have a model that produces multi-year financial output for a product.
The model has an input to change the product under review. I need to
create summary reports based on this financial output for a number of
products, which gets a little tricky as far as I can tell. Originally
I was using a two-way data tables with one axis as the product input
values and the other axis as the values I need for the output, i.e. Y1
Revenue, Y2 Revenue, etc. I was accomplishing this using choose
function for the output axis. However, this involved some manual
copying and pasting and performed somewhat slowly, so I thought VBA
would do the trick. With the help of some folks in this forum, I've
created the following code, which appears to work, but is *extremely*
slow -- even slower than the data table solution. Any suggestions on
how to improve the speed? Here's the code:


Sub ScenOut()
Dim rngNames As Range, rngScenInput As Range
Dim rng1 As Range
Dim cell As Range, cell1 As Range
Dim nm As Name
Dim c As Integer, r As Integer
Dim iMaxScen As Integer
Dim wks As Worksheet

Set wks = Worksheets("Output")
wks.Range("A3").Activate

Set rngNames = Range(Application.Names!OutputNames)
Set rngScenInput = Range(Application.Names("Product.Input"))

c = 1
iMaxScen = 10

Do While c <= iMaxScen
rngScenInput.Value = c
For Each cell In rngNames
r = 0
Set rng1 = Range(cell.Value)
For Each cell1 In rng1
r = r + 1
wks.Cells(r, c).Value = cell1.Value
Next cell1
Next cell
Loop
End Sub

I'm addressing like set rng = Range(Application.Names!DefinedName) per
examples in Steven Roman's book, Writing Excel Macros. Would you
suggest something different?

I turned off screen updating and added incrementing of c inside loop,
but it still runs painfully slow. Any other thoughts?

I did suggest something different.

Only time I have seen Steve post here was when he was asking a question.
Twice as I remember and that was several years ago.

The only way I would see that as being painfully slow would be if your named
ranges had extensive numbers of cells in them and/or if your sheet takes a
long time to recalculate and you macro causes recalculation each time it
writes to the sheet. In that latter case, I would put

Application.Calculation = xlManual
' executable code
Application.Calculation = xlAutomatic


However, since it appears you are doing some kind of whatif, it might be
necessary to have automatic calculation turned on to get the proper results.

If it is just because of numerous cells, then perhaps recording the cell
values in an array and then writing them all at once to the sheet would be
faster.



--
Regards,
Tom Ogilvy



"sloth" wrote:

I'm addressing like set rng = Range(Application.Names!DefinedName) per
examples in Steven Roman's book, Writing Excel Macros. Would you
suggest something different?

I turned off screen updating and added incrementing of c inside loop,
but it still runs painfully slow. Any other thoughts?