# VBA script to add formulas to Excel is running slow

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

• 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/… – t3chb0t Jun 21 '17 at 14:04
• 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! – Part_Time_Nerd Jun 21 '17 at 14:22
• @t3chb0t: could you please add your solution as an answer so the question could be "officially" answered? – Máté Juhász Jun 26 '17 at 10:06

Application.ScreenUpdating = False