Improve VBA function run-time - replace For/Next Loop

[grexcelman]

I have the following code which operates as it should, taking a text string in the adjacent cell and converting it to a formula and applying it to a few other referenced cells. I currently only have about 20 rows of data but I anticipate having nearly 1,000. The run-time is already too slow...I can only imagine how bad it would be trying to evaluate 1,000 cells. Any advice on how to convert this code into a faster function or UDF?

Sub randcalc()
Application.ScreenUpdating = False

Dim i As Long
    
    For i = Range("D" & Rows.Count).End(xlUp).Row To 31 Step -1
Range("N" & i).Select
foutput = "=" & ActiveCell.Offset(0, -1).Range("A1").Value & "(RC[2]:RC[5])"

ActiveCell.FormulaR1C1 = foutput
    Next i

 

[CalcSux78]

From personal experience, avoid ".Select" as it really can bog down a script. Instead of having foutput reference ActiveCell, try something like:

Range("N" & i) = Range("M" & i).Range("A1").Value & "(RC[2]:RC[5])"

I am not able to test, but give it a shot and let us know what happens.

 

[grexcelman]

From personal experience, avoid ".Select" as it really can bog down a script. Instead of having foutput reference ActiveCell, try something like:

Range("N" & i) = Range("M" & i).Range("A1").Value & "(RC[2]:RC[5])"

I am not able to test, but give it a shot and let us know what happens.

Thanks for your reply. The runtime seems about the same but also after removing the fouput reference it no long works as a formula, it simply prints the string as text into the cell. my original function would take the text in the adjacent cell, M, and apply in N as a formula.

I've tried the following but still prints the string as text.

Range("N" & i).FormulaR1C1 = Range("M" & i).Range("A1").Value & "(RC[2]:RC[5])"