VBA script to add formulas to Excel is running slow

Question

I have written the following VBA script that searches for a text string in column CI and if it finds it the specific text the script adds a formula to a series of cells. The script itself runs correctly but I think it is taking much longer to run than it should. I am assuming there are some form efficient ways to write the code.

I have other scripts in the same Excel workbook that take 30 seconds to run but this script takes almost 7 minutes. It is searching through just over 900 cells in the test case I am using. Which in the grand scheme of thing, that is not really that many cells. How can I make it more efficient?

Private Sub Material_Formulas()

Dim lRow As Integer
Dim tiesMaterial As String
Dim result As String

lRow = Sheets("Material").Range("A2").End(xlDown).Row
lCol = Sheets("Material").Range("A2").End(xlToRight).Column

'Starts the count at column CU
endCount = lCol - 1

    For c = 99 To endCount
        For r = 3 To lRow

        tiesMaterial = Cells(r, 87).Value


        'Looks to see if the cells starting at CU2 contains a number and then iterates through each cell in row 3 to add a formula
        If tiesMaterial = "TIES MATERIAL" Then

            materialID = Sheets("Material").Cells(r, "CQ").Address(False, False)
            materialYear = Sheets("Material").Cells(2, c).Address(False, False)

            'Starting in cell CU3 it adds the formula =INDEX(BOM_Summary_Array,MATCH(CQ3,BOM_Summary_ID,0),MATCH(CU2,BOM_Summary_Head,0))
            Sheets("Material").Cells(r, c).Formula = "=INDEX(BOM_Summary_Array,MATCH(Material!" & materialID & ",BOM_Summary_ID,0),MATCH(Material!" & materialYear & ",BOM_Summary_Head,0))"

        End If

        Next r
    Next c

End Sub

Answer 1

When working with Excel and writing to cells the most common performance issue come from the sheet being constantly updated in the background while the script is running. In order to gain more speed you should save the state of a few properties for as long as the script is running and restore their values when it finishes.

For example:

Application.ScreenUpdating = False   
Application.DisplayStatusBar = False    
Application.Calculation = xlCalculationManual    
Application.EnableEvents = False

Reference: Excel VBA Performance Coding Best Practices