EXCEPTIONALLY SLOW LOOP ....help!!!
 

I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

.....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

Have you turned screen updating off when populating the sheet? i.e.:

Application.ScreenUpdating = False

For...

Application.ScreenUpdating = True

And your row placement for placing the values is very sparse. I.e.
Rows 2, 23, 44, 65. Is that what you really want?

SteveM

WhytheQ wrote:
I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

You can "drop" the entire array into a range in one line without a loop.
This may help. (I'm having a problem, posted above, in trying to "drop" a 1D
array into a column!)

dim income() as Variant

Worksheets("Income").Range(Cells(?, ?), Cells(?, ?)) = income

just calculate starting and ending row and column numbers "?" to match the
size and position of the income array


"WhytheQ" wrote:

I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

.....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

also turn off calculation

--
Don Guillett
SalesAid Software

"SteveM" wrote in message
oups.com...
Have you turned screen updating off when populating the sheet? i.e.:

Application.ScreenUpdating = False

For...

Application.ScreenUpdating = True

And your row placement for placing the values is very sparse. I.e.
Rows 2, 23, 44, 65. Is that what you really want?

SteveM

WhytheQ wrote:
I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

It takes about the same time as looping through and writing to 700 cells -
which is essentially what you are doing. If you have formulas that reference
these cells, try turning calculation to manual before running it. (or turn
it off in your code at the beginning and back on at the end). Also, make
sure you don't have visible pagebreaks - this can also slow down macros.

Anyway, it was pretty much instantaneous for me - so I assume it has to be
some factor to do with your spreadsheet.

--
Regards,
Tom Ogilvy


"WhytheQ" wrote:

I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

.....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

Hi Jason


Option Explicit

'Notice @@@.
'Test with test01, please.
'Just to ensure no probleme with your array income.

Sub test01()
Dim i As Long
Dim j As Long

For i = 1 To 35
For j = 1 To 20
Worksheets("Income") _
.Cells(20 * (i - 1) + j + 1, 200) = i * j '@@@
Next j
Next i

End Sub

' <<<<<<<<<<

Sub test02()
'Used time: 00:00:00 = less than 1 second.
Dim TimeStart As Date
Dim TimeEnd As Date
Dim timeused As Date

Dim i As Long
Dim j As Long
Dim income() As Variant


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

TimeStart = Time

For i = 1 To 35
For j = 1 To 20
ReDim Preserve income(1 To 35, 1 To 20)
income(i, j) = Cells(i, j).Value
Next j
Next i

For i = 1 To 35
For j = 1 To 20
Worksheets("Income") _
.Cells(20 * (i - 1) + j + 1, 200) = income(i, j)
Next j
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

TimeEnd = Time

timeused = TimeEnd - TimeStart

Application.StatusBar = "Finito: " _
& Format(timeused, "hh:mm:ss")

End Sub



--
Best Regards
Joergen Bondesen


"WhytheQ" wrote in message
oups.com...
I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason

EXCEPTIONALLY SLOW LOOP ....help!!!
 

Thanks everybody!!
The automatic calculation was the big problem.

Regards
Jason.


Joergen Bondesen wrote:

Hi Jason


Option Explicit

'Notice @@@.
'Test with test01, please.
'Just to ensure no probleme with your array income.

Sub test01()
Dim i As Long
Dim j As Long

For i = 1 To 35
For j = 1 To 20
Worksheets("Income") _
.Cells(20 * (i - 1) + j + 1, 200) = i * j '@@@
Next j
Next i

End Sub

' <<<<<<<<<<

Sub test02()
'Used time: 00:00:00 = less than 1 second.
Dim TimeStart As Date
Dim TimeEnd As Date
Dim timeused As Date

Dim i As Long
Dim j As Long
Dim income() As Variant


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

TimeStart = Time

For i = 1 To 35
For j = 1 To 20
ReDim Preserve income(1 To 35, 1 To 20)
income(i, j) = Cells(i, j).Value
Next j
Next i

For i = 1 To 35
For j = 1 To 20
Worksheets("Income") _
.Cells(20 * (i - 1) + j + 1, 200) = income(i, j)
Next j
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

TimeEnd = Time

timeused = TimeEnd - TimeStart

Application.StatusBar = "Finito: " _
& Format(timeused, "hh:mm:ss")

End Sub



--
Best Regards
Joergen Bondesen


"WhytheQ" wrote in message
oups.com...
I can't see any reason why the following loop should take so long to
execute.....

For i = 1 To 35
For j = 1 To 20
Worksheets("Income").Cells(20* (i - 1) + j + 1, 200) =
income(i, j)
Next j
Next i

....the 2 dimensional array 'income' has been filled, before the above
executes, with the correct numner of elements i.e income(1 to 35,1 to
20).
It's like watching paint dry when the above executes!

Any help greatly appreciated.

Jason