Hi,

If I folllow what you mean maybe something like the following. With this
meaningless calculation, for me the array method was 25 times faster than
working with cells.

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test()
Dim vArr, rng As Range
Dim x As Long, i As Long, j As Long, t As Long
Dim rw As Long, cl As Long

'some data
Set rng = [a1:a10000]
For i = 1 To 5
Set rng = rng.Offset(0, 1)
rng.Value = i
Next

'cells method
t = GetTickCount
For i = 1 To 10000
x = 0
For j = 2 To 6
x = x + Cells(i, j)
Next
Cells(i, 8) = x
Next

Debug.Print "Cells calc " & GetTickCount - t

''Array method
t = GetTickCount
Set rng = [b1:f10000]
vArr = rng
ReDim nArr(1 To rng.Rows.Count, 1 To 1) As Long

rw = rng.Rows.Count
cl = rng.Columns.Count
For i = 1 To rw
x = 0
For j = 1 To cl
x = x + vArr(i, j)
Next
nArr(i, 1) = x
Next

[j1:j10000].Value = nArr
Debug.Print "Array calc " & GetTickCount - t

End Sub



Regards,
Peter


"Cool Sport" wrote in message
...
I have table of raw data (1000 rows and 150 columns) stored in a
worksheet. I'd like to copy them all into a 2-dimension array then do
all the calculation with this array (not the raw data on the sheet).

The calculated result will be in another array (that has the same size
or bigger). This array will be copied back into another sheet (result
sheet).

The problem is that it runs really slow when it calculates values for
every element in result array.

Please anyone can tell me how to speed up or better solution for this
issue.

All the best



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!