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

putting values into cells is slow
 

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" wrote in message
...
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

putting values into cells is slow
 

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" wrote in message
...
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" wrote in message
...
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

putting values into cells is slow
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

putting values into cells is slow
 

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" wrote in message
...
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" wrote in message
...
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

putting values into cells is slow
 

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" wrote in message ...
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" wrote in message
...
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" wrote in message
...
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