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

(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
        .Range("Results").Calculate
        .Range("Results").Copy
        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 *