Speed up macro of index match formulas running

Apple08

Active Member
Joined
Nov 1, 2014
Messages
441
Hi All

I have a set of macro below to insert the index match formulas. However this takes a few hours to finish the macro. Please can anyone help to speed up the macro running time? Many thanks.

VBA Code:
Private Sub IndexMatch()

Dim LastRow As Long
    
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

'B20
    Range("J5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($J$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("K5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($K$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("L5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($L$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("J5:J" & LastRow).FillDown
    Range("K5:K" & LastRow).FillDown
    Range("L5:L" & LastRow).FillDown
    
    Range("J5:L" & LastRow).NumberFormat = "dd/mm/yyy"
    Range("N5:P" & LastRow).NumberFormat = "dd/mm/yyy"
    Range("R5:T" & LastRow).NumberFormat = "dd/mm/yyy"
    Range("V5:X" & LastRow).NumberFormat = "dd/mm/yyy"
    Range("Z5:AB" & LastRow).NumberFormat = "dd/mm/yyy"
    Range("AD5:AF" & LastRow).NumberFormat = "dd/mm/yyy"
  
 'B30
    Range("N5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($N$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("O5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($O$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("P5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($P$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("N5:N" & LastRow).FillDown
    Range("O5:O" & LastRow).FillDown
    Range("P5:P" & LastRow).FillDown

'B35
    Range("R5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($R$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("S5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($S$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("T5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($T$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("R5:R" & LastRow).FillDown
    Range("S5:S" & LastRow).FillDown
    Range("T5:T" & LastRow).FillDown
    
 'H20
    Range("V5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($V$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("W5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($W$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("X5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($X$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("V5:V" & LastRow).FillDown
    Range("W5:W" & LastRow).FillDown
    Range("X5:X" & LastRow).FillDown
    
 'J20
    Range("Z5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($Z$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("AA5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($AA$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("AB5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($AB$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("Z5:Z" & LastRow).FillDown
    Range("AA5:AA" & LastRow).FillDown
    Range("AB5:AB" & LastRow).FillDown
    
'P20
    Range("AD5").FormulaArray = "=iferror(index('All faculties results'!$J:$J,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($AD$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("AE5").FormulaArray = "=iferror(index('All faculties results'!$K:$K,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($AE$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("AF5").FormulaArray = "=iferror(index('All faculties results'!$L:$L,match(1,($D5='All faculties results'!$D:$D)*($E5='All faculties results'!$E:$E)*($H5='All faculties results'!$H:$H)*($AF$4='All faculties results'!$I:$I),0)),"""")" & _
        ""
    Range("AD5:AD" & LastRow).FillDown
    Range("AE5:AE" & LastRow).FillDown
    Range("AF5:AF" & LastRow).FillDown
    
    Columns("J:AG").Select
    ActiveWindow.DisplayZeros = False

End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,076
Office Version
  1. 365
Platform
  1. Windows
A few things that may help.

1. Place these line at the very beginning of your code (under the "Sub ..." line):
VBA Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
and then place these lines at the bottom of your code (just above the "End Sub" line):
VBA Code:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

2. Update your formula so that they don't use a complete column reference like D:D, but instead find the last row and use a defined range, like D2:D1000.
You already are calculating the last row for column A in your code, so I see that you are already familiar with this concept.
Now, just apply it to the ranges in your Index/Match formulas.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
441
Good spot JohnnyL, these columns were added into the template before but they are no longer required, therefore I have another macro to remove these columns after the formula macro:

VBA Code:
Private Sub DelColumn()

  Range("M:M,Q:Q,U:U,Y:Y,AC:AC,AG:AG").Delete

End Sub
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
Good spot JohnnyL, these columns were added into the template before but they are no longer required, therefore I have another macro to remove these columns after the formula macro:

VBA Code:
Private Sub DelColumn()

  Range("M:M,Q:Q,U:U,Y:Y,AC:AC,AG:AG").Delete

End Sub
If delete first, then code, is the code able to be shorter?
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
441
if delete first then it will mess up the column order in the index match formulas...
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
441
I just wonder should I group multiple ranges FillDown after formulas have been added to row 5? I have split up the Range & LastRow for FillDown as I don't know how to simply multiple ranges with last row together.
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
Your code are working with multiple different formulas in : J,K,L then N,O,P then R,S,T,...
After deleting M:M,Q:Q,U:U,Y:Y,AC:AC,AG:AG, now your range is in a solid block from J to AA
Basically, your formula try to index other sheet, column J,K,L; in condition of first match column D,E,H,I with cell in D,E,H,I and column I with cell in row4.
With no worksheet built-in array-formula, I believe VBA code can do that in one go.
Will come back soon.
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,938
Office Version
  1. 2016
Could you post sample worksheet via XL2BB tool?
 

Forum statistics

Threads
1,181,082
Messages
5,927,989
Members
436,582
Latest member
hbehairy

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