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

• t3chb0t: You need to disable UI refreshing and automatic recalculation on each cell update for as long as the loop is running. Here is everything you should need to improve it blogs.office.com/2009/03/12/…
• Part_Time_Nerd: Holy crap where have you been the last week @t3chb0t? I may have just posted this question but I have been struggling with it for the last week! I do not know if I feel more relieved to have a solution or stupid because the answer was so simple. Thanks @t3chb0t you're a lifesaver!
• Máté Juhász: @t3chb0t: could you please add your solution as an answer so the question could be "officially" answered?

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