Speed up macro of index match formulas running

[Apple08]

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.

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

 

[Joe4]

A few things that may help.

1. Place these line at the very beginning of your code (under the "Sub ..." line):

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then place these lines at the bottom of your code (just above the "End Sub" line):

    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]

Thank you Joe4, I will have a go

 

[johnnyL]

@Apple08, Are Columns M ,Q, U, Y, & AC blank columns?

 

[Apple08]

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:

Private Sub DelColumn()

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

End Sub

 

[bebo021999]

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:

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]

if delete first then it will mess up the column order in the index match formulas...

 

[Apple08]

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]

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]

Could you post sample worksheet via XL2BB tool?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com