dreen
Board Regular
- Joined
- Nov 20, 2019
- Messages
- 52
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:
VBA Code:
'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
