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.
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