Increasing the speed of Index & Match in VBA

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
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
You capture data in E4 or it's formula.
You capture data in E5 or it's formula.

If you capture data in those cells, then they can be a trigger for the change event.
Yes I decided to move the code to the "Operator sheet" (sheet 1) and trigger it with any changes to cells "E4" or "E5" as the combination of these two concatenated produce the value in "H4". What do you mean by capture data in those cells, I'm still new to using events like the Change Event and triggering it. Please let me know how I can execute it, thanks Dante!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
Yes I decided to move the code to the "Operator sheet" (sheet 1) and trigger it with any changes to cells "E4" or "E5" as the combination of these two concatenated produce the value in "H4". What do you mean by capture data in those cells, I'm still new to using events like the Change Event and triggering it. Please let me know how I can execute it, thanks Dante!
That's what I mean, if you enter data in those cells or those cells have formulas.
The cells that trigger the event are the cells in which you capture data. Since cells that have formulas are not modifying the cell, what changes is the result of the formula.
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
That's what I mean, if you enter data in those cells or those cells have formulas.
The cells that trigger the event are the cells in which you capture data. Since cells that have formulas are not modifying the cell, what changes is the result of the formula.
That makes sense, I see what you were trying to say earlier. Thank you for all your help Dante!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,159
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top