putting values into cells is slow

In a program, I have
n=500
application.screenupdating=false
For i=1 to n
cells(i,6).value=cells(i,5).value*35
cells(i+n,6).value=cells(i,4).value*76
next i
application.screenupdating=true

This takes an age to run, especially if n is really large.

There must be a faster (better) way of doing this - can anybody help please?

Thanks
Chris

Chris,

It took less than 1/10 of a second on my xl2002 machine.
Numbers in columns 4 and 5 ran from 10000 to 15000.
There must be something other than the loop causing the hang-up.
'-------------------------
Sub TestLoop()
Dim n As Long
Dim i As Long
Dim StartTime As Single
Dim EndTime As Single
n = 500
Application.ScreenUpdating = False
StartTime = Timer
For i = 1 To n
Cells(i, 6).Value = Cells(i, 5).Value * 35
Cells(i + n, 6).Value = Cells(i, 4).Value * 76
Next i
EndTime = Timer
Application.ScreenUpdating = True
MsgBox EndTime - StartTime & " Seconds"
End Sub
'---------------------------

Jim Cone
San Francisco, USA



"inquirer" <[email protected]> wrote in message
news:%[email protected]...
> In a program, I have
> n=500
> application.screenupdating=false
> For i=1 to n
> cells(i,6).value=cells(i,5).value*35
> cells(i+n,6).value=cells(i,4).value*76
> next i
> application.screenupdating=true
>
> This takes an age to run, especially if n is really large.
>
> There must be a faster (better) way of doing this - can anybody help please?
>
> Thanks
> Chris
>
>

Thanks for your reply Jim
I ran the code you sent as is and it took 88.7 seconds.

I am using a Dell 1.8ghz pc with 384MB ram, windows XP SP2. As far as I
know, there are no viruses etc as I regulalry search for them and use a
firewall.

I haven't noticed any other speed problems before this.
Do you have any idea what I might look for to solve the problem?
Chris

"Jim Cone" <[email protected]> wrote in message
news:[email protected]...
> Chris,
>
> It took less than 1/10 of a second on my xl2002 machine.
> Numbers in columns 4 and 5 ran from 10000 to 15000.
> There must be something other than the loop causing the hang-up.
> '-------------------------
> Sub TestLoop()
> Dim n As Long
> Dim i As Long
> Dim StartTime As Single
> Dim EndTime As Single
> n = 500
> Application.ScreenUpdating = False
> StartTime = Timer
> For i = 1 To n
> Cells(i, 6).Value = Cells(i, 5).Value * 35
> Cells(i + n, 6).Value = Cells(i, 4).Value * 76
> Next i
> EndTime = Timer
> Application.ScreenUpdating = True
> MsgBox EndTime - StartTime & " Seconds"
> End Sub
> '---------------------------
>
> Jim Cone
> San Francisco, USA
>
>
>
> "inquirer" <[email protected]> wrote in message
> news:%[email protected]...
>> In a program, I have
>> n=500
>> application.screenupdating=false
>> For i=1 to n
>> cells(i,6).value=cells(i,5).value*35
>> cells(i+n,6).value=cells(i,4).value*76
>> next i
>> application.screenupdating=true
>>
>> This takes an age to run, especially if n is really large.
>>
>> There must be a faster (better) way of doing this - can anybody help
>> please?
>>
>> Thanks
>> Chris
>>
>>

I think I know what the problem is.
The test code below ran in 0.9 sec when I opened excel and copied the code
into a new workbook.
When I was running it before, I had another workbook open at the same time.
In this other workbook, I had a column which generated 500 random numbers.
It seems that everytime I ran the test code, the random number generator
must have fired up in the other workbook. I deleted the column which
generated the random numbers and reran the test code - 0.9 sec again.

I don't understand why the random number generator in another open workbook
should be triggered by running the test code. Seems weird to me but at least
I know what causes the slowness.

Chris



"inquirer" <[email protected]> wrote in message
news:eFE%[email protected]...
> Thanks for your reply Jim
> I ran the code you sent as is and it took 88.7 seconds.
>
> I am using a Dell 1.8ghz pc with 384MB ram, windows XP SP2. As far as I
> know, there are no viruses etc as I regulalry search for them and use a
> firewall.
>
> I haven't noticed any other speed problems before this.
> Do you have any idea what I might look for to solve the problem?
> Chris
>
> "Jim Cone" <[email protected]> wrote in message
> news:[email protected]...
>> Chris,
>>
>> It took less than 1/10 of a second on my xl2002 machine.
>> Numbers in columns 4 and 5 ran from 10000 to 15000.
>> There must be something other than the loop causing the hang-up.
>> '-------------------------
>> Sub TestLoop()
>> Dim n As Long
>> Dim i As Long
>> Dim StartTime As Single
>> Dim EndTime As Single
>> n = 500
>> Application.ScreenUpdating = False
>> StartTime = Timer
>> For i = 1 To n
>> Cells(i, 6).Value = Cells(i, 5).Value * 35
>> Cells(i + n, 6).Value = Cells(i, 4).Value * 76
>> Next i
>> EndTime = Timer
>> Application.ScreenUpdating = True
>> MsgBox EndTime - StartTime & " Seconds"
>> End Sub
>> '---------------------------
>>
>> Jim Cone
>> San Francisco, USA
>>
>>
>>
>> "inquirer" <[email protected]> wrote in message
>> news:%[email protected]...
>>> In a program, I have
>>> n=500
>>> application.screenupdating=false
>>> For i=1 to n
>>> cells(i,6).value=cells(i,5).value*35
>>> cells(i+n,6).value=cells(i,4).value*76
>>> next i
>>> application.screenupdating=true
>>>
>>> This takes an age to run, especially if n is really large.
>>>
>>> There must be a faster (better) way of doing this - can anybody help
>>> please?
>>>
>>> Thanks
>>> Chris
>>>
>>>
>
>

Reduce your 500 by 50 and run again. Continue to reduce by 50 until
performance improves. You may have to split your routine into two parts.
--
Jim at Eagle


"inquirer" wrote:

> Thanks for your reply Jim
> I ran the code you sent as is and it took 88.7 seconds.
>
> I am using a Dell 1.8ghz pc with 384MB ram, windows XP SP2. As far as I
> know, there are no viruses etc as I regulalry search for them and use a
> firewall.
>
> I haven't noticed any other speed problems before this.
> Do you have any idea what I might look for to solve the problem?
> Chris
>
> "Jim Cone" <[email protected]> wrote in message
> news:[email protected]...
> > Chris,
> >
> > It took less than 1/10 of a second on my xl2002 machine.
> > Numbers in columns 4 and 5 ran from 10000 to 15000.
> > There must be something other than the loop causing the hang-up.
> > '-------------------------
> > Sub TestLoop()
> > Dim n As Long
> > Dim i As Long
> > Dim StartTime As Single
> > Dim EndTime As Single
> > n = 500
> > Application.ScreenUpdating = False
> > StartTime = Timer
> > For i = 1 To n
> > Cells(i, 6).Value = Cells(i, 5).Value * 35
> > Cells(i + n, 6).Value = Cells(i, 4).Value * 76
> > Next i
> > EndTime = Timer
> > Application.ScreenUpdating = True
> > MsgBox EndTime - StartTime & " Seconds"
> > End Sub
> > '---------------------------
> >
> > Jim Cone
> > San Francisco, USA
> >
> >
> >
> > "inquirer" <[email protected]> wrote in message
> > news:%[email protected]...
> >> In a program, I have
> >> n=500
> >> application.screenupdating=false
> >> For i=1 to n
> >> cells(i,6).value=cells(i,5).value*35
> >> cells(i+n,6).value=cells(i,4).value*76
> >> next i
> >> application.screenupdating=true
> >>
> >> This takes an age to run, especially if n is really large.
> >>
> >> There must be a faster (better) way of doing this - can anybody help
> >> please?
> >>
> >> Thanks
> >> Chris
> >>
> >>
>
>
>

Chris,

If you have formulas on the spreadsheet that reference
column 6, then Excel would be attempting to
recalculate the spreadsheet every time a cell changed.

Try adding the following line just below the first screenupdating...
Application.Calculation = xlCalculationManual

Then just before the last screenupdating add...
Application.Calculation = xlCalculationAutomatic

Also, "ActiveSheet.DisplayPageBreaks = False" never hurts.

Regards,
Jim Cone
San Francisco, USA


"inquirer" <[email protected]> wrote in message news:eFE%[email protected]...
> Thanks for your reply Jim
> I ran the code you sent as is and it took 88.7 seconds.
> I am using a Dell 1.8ghz pc with 384MB ram, windows XP SP2. As far as I
> know, there are no viruses etc as I regulalry search for them and use a
> firewall.
> I haven't noticed any other speed problems before this.
> Do you have any idea what I might look for to solve the problem?
> Chris


> "Jim Cone" <[email protected]> wrote in message
> news:[email protected]...
> > Chris,
> > It took less than 1/10 of a second on my xl2002 machine.
> > Numbers in columns 4 and 5 ran from 10000 to 15000.
> > There must be something other than the loop causing the hang-up.
> > '-------------------------
> > Sub TestLoop()
> > Dim n As Long
> > Dim i As Long
> > Dim StartTime As Single
> > Dim EndTime As Single
> > n = 500
> > Application.ScreenUpdating = False
> > StartTime = Timer
> > For i = 1 To n
> > Cells(i, 6).Value = Cells(i, 5).Value * 35
> > Cells(i + n, 6).Value = Cells(i, 4).Value * 76
> > Next i
> > EndTime = Timer
> > Application.ScreenUpdating = True
> > MsgBox EndTime - StartTime & " Seconds"
> > End Sub
> > '---------------------------
> > Jim Cone
> > San Francisco, USA



> > "inquirer" <[email protected]> wrote in message
> > news:%[email protected]...
> >> In a program, I have
> >> n=500
> >> application.screenupdating=false
> >> For i=1 to n
> >> cells(i,6).value=cells(i,5).value*35
> >> cells(i+n,6).value=cells(i,4).value*76
> >> next i
> >> application.screenupdating=true
> >> This takes an age to run, especially if n is really large.
> >> There must be a faster (better) way of doing this - can anybody help
> >> please?
> >> Thanks
> >> Chris