Speed up the loop process (Copy paste Value)

Hi,

I'm still very beginner in VBA.

I have this VBA code below:

Code:

---

Sub Iteration()

---

Code:

---

Application.ScreenUpdating = False
Dim i As Integer
Dim j As Integer
j = Range("C14").Value 'Can be up tp 10,000 iteration
For i = 1 To j
Range(Cells(i + 1, 19), Cells(i + 1, 20)) = Range("P20:Q20").Value
Next i
Application.ScreenUpdating = True
End Sub

---

The loop process or iteration can be up to 10,000, but it will take like 10 minutes to run.
Appreciate if anyone can help me to speed up the process.

Thanks in advance.

There are probably complex formulas in the workbook that refer to the cells being changed by the macro. If so, at the beginning of the procedure, disable automatic calculation of the workbook, and finally enable.

Code:

---

Application.Calculation = xlCalculationManual
(...)
Application.Calculation = xlCalculationAutomatic

---

You can perform this task without iteration:

Code:

---

Sub NoIteration()
    Dim j          As Long

    j = Range("C14").Value

    Range("S2").Resize(j, 2).Value = Range("P20:Q20").Value
End Sub

---

Artik

Hi Artik,

Thank you for the solution.

But the xlcalculation have to be automatic, or the value in P20:Q20 will not change, coz the formula in that cells will generate random number.

Provide the content of formulas in P20 and Q20.

Artik

Maybe just copy the formulas from P20:Q20 and paste in the range S2:Tx, and then in the range S2:Tx turn the formulas into values:

Code:

---

Sub NoIteration_1()
    Dim j          As Long

    j = Range("C14").Value

    With Range("S2").Resize(j, 2)
      .Formula = Range("P20:Q20").Formula
      .Value = .Value
    End With
   
End Sub

---

Artik

Hi Artik,

Sorry for the late reply,

It works perfectly, :thumb

Many thanks for your help.

now its only take 1 second :)