This is the mail I sent to OP just now..

Tim

Been fiddling with your sheet.
Darn.. it's hard to read somebody elses code if you haven't got the
data

So what I did was set up some dummy sheets to see what your macro was
doing exactly.

My advice is following:

In your forecast sheet use cell C4 as the base of an offset formula
the macro will just change this 1 cell and formulas will do the rest!

Then copy a few formulas in c5:c13 to pick up the data from "calls"
C5=OFFSET(calls!E$1,C$4,0)
C6=OFFSET(calls!J$1,C$4,0)
C7=OFFSET(calls!Q$1,C$4,0)
C8=OFFSET(calls!K$1,C$4,0)
C9=OFFSET(calls!AA$1,C$4,0)
C10=OFFSET(calls!AI$1,C$4,0)
C11=OFFSET(calls!BZ$1,C$4,0)
C12=OFFSET(calls!AQ$1,C$4,0)
C13=OFFSET(calls!AP$1,C$4,0)

now your macro gets a bit simpler:

Sub NewFrcst()
'PLEASE NOTE THAT AS FAR AS I CAN SEE
'LOOPING THE LNGCOL changes NOTHING!!!!!!!!!

Dim lngCol As Long
Dim lngRow As Long
Application.ScreenUpdating = False
For lngCol = 0 To 18 Step 2
With [calls!CC:CC].Offset(0, lngCol)
For lngRow = 12 To [calls!E65536].End(xlUp).Row
[frcst!c4] = lngRow
.Cells(lngRow) = [frcst!c20]
Next
End With
Next
Application.ScreenUpdating = True
End Sub


This should improve speed, as only 1 cell gets updated,
so only 1 change event will lead to recalc.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

I looked at his code a bit and he isn't copying any blocks.

("e", "j", "q", "k", "aa", "ai", "bz", "aq", "ap") '1
("e", "j", "q", "k", "aa", "ai", "cb", "aq", "ap") '2
("e", "j", "q", "k", "aa", "ai", "cd", "aq", "ap") '3
("e", "j", "q", "k", "aa", "ai", "cf", "aq", "ap") '4
("e", "j", "q", "k", "aa", "ai", "ch", "aq", "ap") '5
("e", "j", "q", "k", "aa", "ai", "cj", "aq", "ap") '6
("e", "j", "q", "k", "aa", "ai", "cl", "aq", "ap") '7
("e", "j", "q", "k", "aa", "ai", "cn", "aq", "ap") '8
("e", "j", "q", "k", "aa", "ai", "cp", "aq", "ap") '9
("e", "j", "q", "k", "aa", "ai", "cr", "aq", "ap") '10

It looks like you could eliminate the copying of the values from the 8
non changing columns and only copy them once per row.

However, as I stated, running the code with no real work being done,
only involves about 2 minutes - so rearranging the code can reduce
that, but the other 3 hours and 58 minutes is probably the results of
calculation. Besides turning off calculation, any changes in
procedures should focus on reducing the number of calculations - his
calculation occurs once per row and returns one value per row - don't
see a big harvest in rearranging the code.

You might have better ideas, of course.

It's probably more in the sequencing of the loops.
doubt it
PLUS he's copying individual cells rather than ranges.
he needs to
AND he's not disabling events/calculation nor screenupdating.
suggested disabling calculation - didn't consider he might have
events,
his code would have minimal impact from screenupdating from what I can
see, but doesn't hurt if he has the forecast sheet visible.

I didn't run his actual code. I copied one complete set of loops and
put in an outerloop to do that 10 times.

--
Regards,
Tom Ogilvy



keepitcool wrote in message
...
Tom

It's probably more in the sequencing of the loops.
PLUS he's copying individual cells rather than ranges.
AND he's not disabling events/calculation nor screenupdating.

like they say... it's rather confusing :)

at present he's looping
10 blocks of 6000 scenarios of 10 cells

whereas his intention may be:
10 blocks of 10 scenarios of 6000 cells.

i've sent OP a mail. waiting for reaction.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

At the top of the procedure

Application.Calculation = xlManual

at the bottom of the procedure

Application.Calculation = xlAutomatic

running the code on a sheet doing almost no calculation to about
2.5 minutes on my machine - setting calculation to manual reduced
that to a little less than 2 minutes. So trying to minimize the
amount of calculation should be your biggest savings.

--
Regards,
Tom Ogilvy


_______Tim_______ wrote in message
...
Hi,

In a blank workbook I ran my version of his code (1 section looped 10 times
with calculation set to manual) and your code

(my version of his code is not a suggested improvement - just saying the 4
hours is not necessarily produced by sloppy code - yours is certainly an
improvement in volume and clarity).

Time in seconds
28.55469 my version of his code (with screen updating on)
54.8125 your code (with screen updating on)

23.78125 my version of his code (with scren updating off)
11.91406 your code (as written with screenupdating off)


Your code misses the fact that the 7th column changes on each of the loops
through lngCol, but fixing that wouldn't be a big time taxer. Appears
turning screenupdating off is more critical than I suggested - affected by
formula updates I expect.

So the question is if your tighter code with reduced calculation requirement
would be significantly more efficient than just setting calculation to
manual in the existing code.

--
Regards,
Tom Ogilvy



keepitcool wrote in message
...
This is the mail I sent to OP just now..

Tim

Been fiddling with your sheet.
Darn.. it's hard to read somebody elses code if you haven't got the
data

So what I did was set up some dummy sheets to see what your macro was
doing exactly.

My advice is following:

In your forecast sheet use cell C4 as the base of an offset formula
the macro will just change this 1 cell and formulas will do the rest!

Then copy a few formulas in c5:c13 to pick up the data from "calls"
C5=OFFSET(calls!E$1,C$4,0)
C6=OFFSET(calls!J$1,C$4,0)
C7=OFFSET(calls!Q$1,C$4,0)
C8=OFFSET(calls!K$1,C$4,0)
C9=OFFSET(calls!AA$1,C$4,0)
C10=OFFSET(calls!AI$1,C$4,0)
C11=OFFSET(calls!BZ$1,C$4,0)
C12=OFFSET(calls!AQ$1,C$4,0)
C13=OFFSET(calls!AP$1,C$4,0)

now your macro gets a bit simpler:

Sub NewFrcst()
'PLEASE NOTE THAT AS FAR AS I CAN SEE
'LOOPING THE LNGCOL changes NOTHING!!!!!!!!!

Dim lngCol As Long
Dim lngRow As Long
Application.ScreenUpdating = False
For lngCol = 0 To 18 Step 2
With [calls!CC:CC].Offset(0, lngCol)
For lngRow = 12 To [calls!E65536].End(xlUp).Row
[frcst!c4] = lngRow
.Cells(lngRow) = [frcst!c20]
Next
End With
Next
Application.ScreenUpdating = True
End Sub


This should improve speed, as only 1 cell gets updated,
so only 1 change event will lead to recalc.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

I looked at his code a bit and he isn't copying any blocks.

("e", "j", "q", "k", "aa", "ai", "bz", "aq", "ap") '1
("e", "j", "q", "k", "aa", "ai", "cb", "aq", "ap") '2
("e", "j", "q", "k", "aa", "ai", "cd", "aq", "ap") '3
("e", "j", "q", "k", "aa", "ai", "cf", "aq", "ap") '4
("e", "j", "q", "k", "aa", "ai", "ch", "aq", "ap") '5
("e", "j", "q", "k", "aa", "ai", "cj", "aq", "ap") '6
("e", "j", "q", "k", "aa", "ai", "cl", "aq", "ap") '7
("e", "j", "q", "k", "aa", "ai", "cn", "aq", "ap") '8
("e", "j", "q", "k", "aa", "ai", "cp", "aq", "ap") '9
("e", "j", "q", "k", "aa", "ai", "cr", "aq", "ap") '10

It looks like you could eliminate the copying of the values from the 8
non changing columns and only copy them once per row.

However, as I stated, running the code with no real work being done,
only involves about 2 minutes - so rearranging the code can reduce
that, but the other 3 hours and 58 minutes is probably the results of
calculation. Besides turning off calculation, any changes in
procedures should focus on reducing the number of calculations - his
calculation occurs once per row and returns one value per row - don't
see a big harvest in rearranging the code.

You might have better ideas, of course.

It's probably more in the sequencing of the loops.
doubt it
PLUS he's copying individual cells rather than ranges.
he needs to
AND he's not disabling events/calculation nor screenupdating.
suggested disabling calculation - didn't consider he might have
events,
his code would have minimal impact from screenupdating from what I can
see, but doesn't hurt if he has the forecast sheet visible.

I didn't run his actual code. I copied one complete set of loops and
put in an outerloop to do that 10 times.

--
Regards,
Tom Ogilvy



keepitcool wrote in message
...
Tom

It's probably more in the sequencing of the loops.
PLUS he's copying individual cells rather than ranges.
AND he's not disabling events/calculation nor screenupdating.

like they say... it's rather confusing :)

at present he's looping
10 blocks of 6000 scenarios of 10 cells

whereas his intention may be:
10 blocks of 10 scenarios of 6000 cells.

i've sent OP a mail. waiting for reaction.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote:

At the top of the procedure

Application.Calculation = xlManual

at the bottom of the procedure

Application.Calculation = xlAutomatic

running the code on a sheet doing almost no calculation to about
2.5 minutes on my machine - setting calculation to manual reduced
that to a little less than 2 minutes. So trying to minimize the
amount of calculation should be your biggest savings.

--
Regards,
Tom Ogilvy


_______Tim_______ wrote in message
...
Hi,