Hi,

I would like to know what is the fastest method in changing cell value,
currently, my code is :

---------------------------

myCol = 97
myRow = 1

for i = 0 to 5000
for j = 0 to 20
Range(chr(myCol)&myRow) = "TEST"
next
next

---------------------------

This seems to take 30 second to finish, any improvement can be done? or
the limitation of excel?

Thanks!

Nick

Some Corrections..

---------------

myRow = 1

for i = 0 to 5000
myCol = 97 ' 97 mean a
for j = 0 to 20
Range(chr(myCol)&myRow) = "TEST"
myCol = myCol + 1
next
myRow = myRow + 1
next
---------------


Nick wrote:

Hi,

I would like to know what is the fastest method in changing cell value,
currently, my code is :

---------------------------

myCol = 97
myRow = 1

for i = 0 to 5000
for j = 0 to 20
Range(chr(myCol)&myRow) = "TEST"
next
next

---------------------------

This seems to take 30 second to finish, any improvement can be done? or
the limitation of excel?

Thanks!

Nick

Grüezi Nick

Nick schrieb am 17.08.2004

I would like to know what is the fastest method in changing cell value,
currently, my code is :

---------------------------

myCol = 97
myRow = 1

for i = 0 to 5000
for j = 0 to 20
Range(chr(myCol)&myRow) = "TEST"
next
next

---------------------------

This seems to take 30 second to finish, any improvement can be done? or
the limitation of excel?

Actually the following des the same like your code:

Range("A1").Value = "TEST"

....but I think you would fill an etire area or range with a value.
Try it like this:

Range("A1:A100").Value = "TEST"

...or try to explain some more, what you like to do
(in your code you have two count-variables you don't use somewhere)

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

Grüezi Nick

Nick schrieb am 17.08.2004

Some Corrections..

Ahhh, now its clear :-)

You will fill a Range of 20 Columns and 5000 Rows, beginning in A1.

You can do this like the following:

mycol = 20
myrow = 1000
Range("A1").Resize(myrow, mycol).Value = "TEST"

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

Hi Thomas,

Thanks for your reply.

In fact, the value going to set is not a static text

for the below code, how it can be optimized ?
---------------------------------------------------------
myRow = 1

for i = 0 to 5000
myCol = 97 ' 97 mean a
for j = 0 to 20
Range(chr(myCol)&myRow) = i & j 'not static!
myCol = myCol + 1
next
myRow = myRow + 1
next
----------------------------------------------------------

Thanks again.

Nick


Thomas Ramel wrote:
Grüezi Nick

Nick schrieb am 17.08.2004


Some Corrections..


Ahhh, now its clear :-)

You will fill a Range of 20 Columns and 5000 Rows, beginning in A1.

You can do this like the following:

mycol = 20
myrow = 1000
Range("A1").Resize(myrow, mycol).Value = "TEST"

Sub Tester1()
Dim varr() As String
Dim i As Long, j As Long

ReDim varr(0 To 5000, 0 To 20)
For i = 0 To 5000
For j = 0 To 20
varr(i, j) = i & j 'not static!
Next
Next
Application.ScreenUpdating = False
Application.Calculation = xlManual
Range("A1").Resize(50001, 21).Value = varr
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


took about 20 seconds for me, but most of the time was spend writing to the
worksheet. Building the array was almost instantaneous.

--
Regards,
Tom Ogilvy


"Nick" wrote in message
...
Hi Thomas,

Thanks for your reply.

In fact, the value going to set is not a static text

for the below code, how it can be optimized ?
---------------------------------------------------------
myRow = 1

for i = 0 to 5000
myCol = 97 ' 97 mean a
for j = 0 to 20
Range(chr(myCol)&myRow) = i & j 'not static!
myCol = myCol + 1
next
myRow = myRow + 1
next
----------------------------------------------------------

Thanks again.

Nick


Thomas Ramel wrote:
Grüezi Nick

Nick schrieb am 17.08.2004


Some Corrections..


Ahhh, now its clear :-)

You will fill a Range of 20 Columns and 5000 Rows, beginning in A1.

You can do this like the following:

mycol = 20
myrow = 1000
Range("A1").Resize(myrow, mycol).Value = "TEST"

Hi,

After adding Application.ScreenUpdating + Application.Calculation,
performance is better, thanks!

But, it might be silly, what is the exact function of ReSize method?

Nick

Tom Ogilvy wrote:

Sub Tester1()
Dim varr() As String
Dim i As Long, j As Long

ReDim varr(0 To 5000, 0 To 20)
For i = 0 To 5000
For j = 0 To 20
varr(i, j) = i & j 'not static!
Next
Next
Application.ScreenUpdating = False
Application.Calculation = xlManual
Range("A1").Resize(50001, 21).Value = varr
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


took about 20 seconds for me, but most of the time was spend writing to the
worksheet. Building the array was almost instantaneous.

to re-size the range

Range("A1").Resize(10,2)

then refers to A1:B10

as an example.

--
Regards,
Tom Ogilvy


"Nick" wrote in message
...
Hi,

After adding Application.ScreenUpdating + Application.Calculation,
performance is better, thanks!

But, it might be silly, what is the exact function of ReSize method?

Nick

Tom Ogilvy wrote:

Sub Tester1()
Dim varr() As String
Dim i As Long, j As Long

ReDim varr(0 To 5000, 0 To 20)
For i = 0 To 5000
For j = 0 To 20
varr(i, j) = i & j 'not static!
Next
Next
Application.ScreenUpdating = False
Application.Calculation = xlManual
Range("A1").Resize(50001, 21).Value = varr
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


took about 20 seconds for me, but most of the time was spend writing to
the
worksheet. Building the array was almost instantaneous.