PDA

View Full Version : Speed up the loop process (Copy paste Value)



coes
06-06-2019, 04:37 PM
Hi,

I'm still very beginner in VBA.

I have this VBA code below:


Sub Iteration()
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.

Artik
06-06-2019, 05:56 PM
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.

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

You can perform this task without iteration:
Sub NoIteration()
Dim j As Long

j = Range("C14").Value

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


Artik

coes
06-07-2019, 03:46 AM
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.

Artik
06-07-2019, 04:18 AM
Provide the content of formulas in P20 and Q20.

Artik

Artik
06-07-2019, 02:18 PM
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:
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

coes
06-07-2019, 03:45 PM
Hi Artik,

Sorry for the late reply,

It works perfectly, :thumb

Many thanks for your help.

now its only take 1 second :)