excel fill formula till last row

Question

I have below vba code to put a formula in cell AE3 then copy down to last row, i have 5000+ rows but wonder why it take so long to process (about 5 min. and still running), is there a better way to do this? i want copy down to last row as the list is dynamic data with different ranges every day. Thanks.

 Sub FillRows()
 Dim col_AE As String
 Sheet1.Select

col_AE = "=IFERROR(INDEX(setting!C[-17],MATCH(smart!RC[-9],setting!C[-18],0)),"""")"
 If col_AE <> vbNullString Then
    For j = 3 To Range("A" & Rows.Count).End(xlUp).Row - 1
        If Range("ae" & j).Value = vbNullString Then
            Range("ae" & j).Value = col_AE
        End If
    Next j
   End If
End Sub

Answer 1

You should turn off both ScreenUpdating and Calculations when working with large numbers of formulas.

This line If col_AE <> vbNullString Then isn't doing anything.

Option Explicit

Sub FillRows()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim col_AE As String

    With Sheet1

        For j = 3 To .Range("A" & .Rows.Count).End(xlUp).Row - 1
            If .Range("ae" & j).Value = vbNullString Then
                .Range("ae" & j).FormulaR1C1 = "=IFERROR(INDEX(setting!C[-17],MATCH(smart!RC[-9],setting!C[-18],0)),"""")"
            End If
        Next j

    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

---

The majority of the processing time is being used because the sheet is recalculating every time a formula is added. I would just turn off ScreenUpdating and Calculations and replace all the formulas. In this way I know that the formulas are consistent and that any errors introduced by users would be corrected.

Sub FillRows2()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim col_AE As String

    With Sheet1
        .Range("A3", "A" & .Rows.Count).End(xlUp).FormulaR1C1 = "=IFERROR(INDEX(setting!C[-17],MATCH(smart!RC[-9],setting!C[-18],0)),"""")"
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Answer 2

This might speed it up - turn off the screen updating while it is running.

Application.ScreenUpdating = False
Application.ScreenUpdating = True

Answer 3

Please try this:

 Option Explicit

Sub fillFormula()


Dim wbk1 As Workbook
Dim lastRow As Long


Set wbk1 = ActiveWorkbook

    With wbk1.Sheets("sheet1")

      lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
      lastRow = lastRow - 1


      .Range("AE3:AE" & lastRow).Formula = _
       "=IFERROR(INDEX(setting!C[-17],MATCH(smart!RC[-9]," _
       & "setting!C[-18],0)),"""")"



    End With




End Sub