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