(Solved) – Increase speed of Excel VBA loop for large number of iterations


My VBA code currently loops for 5,000 iterations and takes about 25 minutes to collect the output data in the Output excel sheet. Is there a way to reduce the time taken for instance by improving the copying and pasting of values, collecting the output data in some kind of array etc.? Given that I will be looping it for up to 10,000 iterations when I obtain more data.

Note: The calculations are excel formulas within the Calcs excel sheet, as this is a cash flow schedule with irregular cash flows and discount rates, hence I am unable to automate the cash flow calculations via VBA

Sub x()

Dim r As Long
Dim lastrow As Long

Application.ScreenUpdating = False
Application.CutCopyMode = False

With Worksheets("MCInput")
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
MsgBox lastrow - 1 & " Trials"
End With

With Worksheets("Calcs")
    For r = 0 To (lastrow - 2)
        .Range("CASHINPUT").Value = Worksheets("MCInput").Range("TCASHRTN").Offset(r).Value
        .Range("EQINPUT").Value = Worksheets("MCInput").Range("TEQRTN").Offset(r).Value
        .Range("FIINPUT").Value = Worksheets("MCInput").Range("TFIRTN").Offset(r).Value
        Worksheets("Output").Range("A1").Offset(1   r, 1).PasteSpecial xlPasteValues
    Next r
End With

Application.ScreenUpdating = True
Application.CutCopyMode = True

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *