Loop to fill array and then paste it slows down way too much

[GeeWhiz7]

Hi guys,
I'm struggling to find my error in the code below. It was working a while back, but now I've changed something and it no longer works and just never really completes. It doesn't throw an error, but takes forever to write the array out to the worksheet when it used to be very fast.

The code being run is below and simply assigns a value to Sheet4 "L16", then passes the values that result from other calculations in Columns N51:Z51 to MyArray. Once done with looping through the X variable (40 to 160 in Steps of 2 so 61 total values that will equal 61 rows)

The array seems to be getting filled, but when I want to pass it to Sheet16 with the i,j loop, it takes forever to write the values. I'm lost.

Sub SPP_A()

Dim t As Date
Dim MyArray(60, 12) As Variant
Dim x As Long
Dim i As Long
Dim j As Long
 
    t = Now()
    
    Application.ScreenUpdating = False
             
    Sheet4.Activate

    OpenStatusBar
  
'Loop assigns value to L16, then puts results of cells in N51 to Z51 into MyArray 
   For x = 40 To 160 Step 2

        Sheet4.Range("L16") = x
        MyArray(i, 0) = x
        MyArray(i, 1) = Range("N51").Value
        MyArray(i, 2) = Range("O51").Value
        MyArray(i, 3) = Range("P51").Value
        MyArray(i, 4) = Range("Q51").Value
        MyArray(i, 5) = Range("R51").Value
        MyArray(i, 6) = Range("S51").Value
        MyArray(i, 7) = Range("T51").Value
        MyArray(i, 8) = Range("U51").Value
        MyArray(i, 9) = Range("V51").Value
        MyArray(i, 10) = Range("W51").Value
        MyArray(i, 11) = Range("X51").Value
        MyArray(i, 12) = Range("Z51").Value
           
        i = i + 1
       
    DoEvents
    Call RunStatusBar(x, 120)
        
    Next x
    
'Activate sheet to report the values from MyArray
    Sheet16.Activate

'Loop through the array and print the values into the summary sheet    
    For i = 0 To 60
    
        For j = 0 To 12
            Cells(i + 1, j + 1).Value = MyArray(i, j)
        Next j
        
    Next i
    
    Application.ScreenUpdating = True
   
    MsgBox "Total calculation time for SPP in Hrs:Min:Sec: " & Format(Now() - t, "hh:mm:ss")

End Sub

Here is what the report sheet looks like...It correctly starts to get the data from the array but not in an acceptable amount of time..
Anybody spot what I am doing wrong?
Thanks!

40​

15​

4​

11​

0.292387​

-0.2631​

0.029291​

0.820783​

0.001953​

14.13333​

-0.28052​

-0.66498​

1.039656​

42​

16​

5​

11​

0.324826​

-0.14214​

0.182684​

0.820783​

0.011418​

15.0625​

-0.28052​

-0.66447​

1.277924​

44​

15​

4​

11​

0.289735​

-0.12924​

0.160492​

0.820783​

0.010699​

14.73333​

-0.28052​

-0.66447​

1.249049​

46​

14​

4​

10​

0.274595​

-0.07964​

0.194955​

0.820783​

0.013925​

14​

-0.28052​

-0.61963​

1.327758​

48​

13​

3​

10​

0.281611​

-0.05287​

0.228744​

0.820783​

0.017596​

14.69231​

-0.28052​

-0.6765​

1.390884​

50​

13​

3​

10​

0.281611​

-0.00872​

0.272891​

0.820783​

0.020992​

14.46154​

-0.28052​

-0.6765​

1.466323​

52​

13​

3​

10​

0.311874​

-0.00826​

0.303612​

0.820783​

0.023355​

14.69231​

-0.28052​

-0.6765​

1.519226​

54​

13​

3​

10​

0.376348​

-0.00826​

0.368087​

0.820783​

0.028314​

14.61538​

-0.28052​

-0.6765​

1.629488​

56​

11​

3​

8​

0.376348​

-0.07545​

0.300897​

0.820783​

0.027354​

17.36364​

-0.28052​

-0.6765​

1.518376​

58​

11​

3​

8​

0.376348​

-0.07545​

0.300897​

0.820783​

0.027354​

16.18182​

-0.28052​

-0.6765​

1.518376​

60​

10​

2​

8​

0.259531​

-0.06504​

0.194495​

0.820783​

 

[offthelip]

A really quick way to make it much faster is to replace:

   For i = 0 To 60
    
        For j = 0 To 12
            Cells(i + 1, j + 1).Value = myarray(i, j)
        Next j
        
    Next i

with

Range(Cells(1, 1), Cells(60, 12)) = myarray

 

[offthelip]

this change should improve it a bit too:

   For x = 40 To 160 Step 2

        Sheet4.Range("L16") = x
        Temparr = Range("N51:Z51")
        MyArray(i, 0) = x
        MyArray(i, 1) = Temparr(1, 1) 'Range("N51").Value
        MyArray(i, 2) = Temparr(1, 2) ' Range("O51").Value
        MyArray(i, 3) = Temparr(1, 3) ' Range("P51").Value
        MyArray(i, 4) = Temparr(1, 4) ' Range("Q51").Value
        MyArray(i, 5) = Temparr(1, 5) ' Range("R51").Value
        MyArray(i, 6) = Temparr(1, 6) ' Range("S51").Value
        MyArray(i, 7) = Temparr(1, 7) ' Range("T51").Value
        MyArray(i, 8) = Temparr(1, 8) ' Range("U51").Value
        MyArray(i, 9) = Temparr(1, 9) ' Range("V51").Value
        MyArray(i, 10) = Temparr(1, 10) ' Range("W51").Value
        MyArray(i, 11) = Temparr(1, 11) ' Range("X51").Value
        MyArray(i, 12) = Temparr(1, 12) ' Range("Z51").Value

The reason it might take ages is because everytime you write to the worksheet it might be causing a recalculation which could be getting slower all the time. To write fast VBA Avoid writing to the worksheet in a loop because it is always very slow

 

[GeeWhiz7]

this change should improve it a bit too:

   For x = 40 To 160 Step 2

        Sheet4.Range("L16") = x
        Temparr = Range("N51:Z51")
        MyArray(i, 0) = x
        MyArray(i, 1) = Temparr(1, 1) 'Range("N51").Value
        MyArray(i, 2) = Temparr(1, 2) ' Range("O51").Value
        MyArray(i, 3) = Temparr(1, 3) ' Range("P51").Value
        MyArray(i, 4) = Temparr(1, 4) ' Range("Q51").Value
        MyArray(i, 5) = Temparr(1, 5) ' Range("R51").Value
        MyArray(i, 6) = Temparr(1, 6) ' Range("S51").Value
        MyArray(i, 7) = Temparr(1, 7) ' Range("T51").Value
        MyArray(i, 8) = Temparr(1, 8) ' Range("U51").Value
        MyArray(i, 9) = Temparr(1, 9) ' Range("V51").Value
        MyArray(i, 10) = Temparr(1, 10) ' Range("W51").Value
        MyArray(i, 11) = Temparr(1, 11) ' Range("X51").Value
        MyArray(i, 12) = Temparr(1, 12) ' Range("Z51").Value

The reason it might take ages is because everytime you write to the worksheet it might be causing a recalculation which could be getting slower all the time. To write fast VBA Avoid writing to the worksheet in a loop because it is always very slow

this works nicely and halves the typical run time I was seeing!! Thanks for the code and the info offthelip!