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

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

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
>
>

also turn off calculation

--
Don Guillett
SalesAid Software
[email protected]
"SteveM" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
> news:[email protected]...
> >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