- Nov 20, 2019
My code works and executes exactly what I need it to do, but I would like to improve it's processing time as updating each individual cell is significantly slowing down my macro.
Currently, I am using VBA to do an Index & Match formula with another workbook where it retrieves information (parameters) from. I believe the slow processing time is because I'm updating the values in each individual cell using Index and Match by using a Range. I am trying to mass load Variant arrays instead as an alternate solution but I am struggling with incorporating it into my code. If there is also another method of increasing the processing time please let me know, variant arrays was just a method I was trying.
Here is my current code using Index & Match with Range:
'This macro uses an Index and Match application to fill in the "Changes Pending Approval" parameters in the "Operator" worksheet Private Sub Worksheet_Calculate() 'Occurs after the worksheet is recalculated for the Worksheet object (any changes in the intersect cell) Application.ScreenUpdating = False 'This speeds up the macro by hiding what the macro is doing If Not Intersect(Range("H4"), Range("H4")) Is Nothing Then 'Checking if the "Key" (part or process) has been changed Dim yChanges As Worksheet, OperatorWs As Worksheet 'Declaring worksheets as variables Dim yChangesLastRow As Long, Parameters As Long, x As Long, z As Long 'Declaring variables to count last rows and "x" & "z" as integers (long variables) Set y = Workbooks.Open(Filename:="\Databases\Database_IRR 200-2S.xlsm", Password:="Swarf") 'Sets the Workbook variable as the database filepath Set yChanges = y.Sheets("Changes") 'Sets the Worksheet variable as the "Changes" sheet in the database's workbook Set OperatorWs = ThisWorkbook.Worksheets("Operator") 'Sets the Worksheet variable as the "Operator" sheet in this workbook OperatorWs.Unprotect "123" 'Unprotects the "Operator" sheet Parameters = yChanges.Range("F1:CL1").Columns.Count 'Counts the number of columns in the "Changes" sheet yChangesLastRow = yChanges.Range("A" & Rows.Count).End(xlUp).Row 'Finds the last row in the "Changes" sheet & counts the number of rows yChangesLastRow = yChangesLastRow - 2 '-2 from the number of rows to account for the header & Vlookup (2nd) column z = 6 'Sets variable "z" to start with the first parameter in the "Changes" sheet (Column "F") For x = 31 To Parameters + 31 'Sets variable "x" to start from the first Parameter in the "Operator" sheet to the last row With yChanges 'With the "Changes" sheet do the following Dim IndexRng As Range 'Declaring variable as a range Set IndexRng = .Range(.Cells(3, z), .Cells(yChangesLastRow, z)) 'Sets range variable as the index lookup array (Pending Changes entries) Dim MatchRng As Range 'Declaring variable as a range Set MatchRng = .Range("A3:A" & yChangesLastRow) 'Sets range variable as the match lookup array (Pending changes "Keys" only) End With 'Ending the "With statement" Dim matchNum As Variant 'Declaring variable as general datatype matchNum = Application.Match(Sheet1.Range("H4").Value, MatchRng, 0) 'Sets variable equal to the Match function to find the "Key" in the "Changes" sheet If Not IsError(matchNum) Then 'Checking if the "Key" is in the "Changes" sheet (True or False) OperatorWs.Range("N" & x).Value = Application.Index(IndexRng, matchNum) 'True: Sets the Changes Pending Approval parameters in the "Operator" sheet Else 'False: no match was found for the "Key" (Part & Process) in the "Changes sheet Exit Sub 'End the macro End If 'End the "IF" statement z = z + 1 '+1 to execute the "For" statement with the next (lookup) parameter Next x 'Executes the "For" statement with the next "x" value (+1 until it reaches the "Parameters + 31" integer) OperatorWs.Protect "123" 'Protect the "Operator" sheet y.Save 'Save the database Workbook y.Close False 'Close the database Workbook End If 'End the "IF" statement Application.ScreenUpdating = True 'Must be "True" after running the code to be able to Read/Write the Workbook End Sub 'End the macro