Help to speed up Macro

[dlatha01]

Hi All,

Any help appreciated to speed up this macro would be greatly appreciated.

Many Thanks

Sub FTSE()

'

' Macro Currently running at 42 seconds

Application.ScreenUpdating = False

Application.EnableEvents = False

Range("V11").Select

Selection.FormulaArray = _

"=IFERROR(INDEX(Ftse!C[-4],MATCH(MAX(IF(RC[-21]=Ftse!C[-20],Ftse!C[-16])),IF(RC[-21]=Ftse!C[-20],Ftse!C[-16]),0)),0)"

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("W11").Select

Selection.FormulaArray = _

"=IFERROR(INDEX(Ftse!C[-15],MATCH(MAX(IF(RC[-22]=Ftse!C[-21],Ftse!C[-17])),IF(RC[-22]=Ftse!C[-21],Ftse!C[-17]),0)),0)"

Selection.FormulaArray = _

"=IFERROR(INDEX(Ftse!C[-15],MATCH(MAX(IF(RC[-22]=Ftse!C[-21],Ftse!C[-17])),IF(RC[-22]=Ftse!C[-21],Ftse!C[-17]),0)),0)"

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("X11").Select

ActiveCell.FormulaR1C1 = _

"=INDEX(Positions!C[-22],MATCH([@[FundID]],Positions!C[-16],0))"

Range("X11").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

 

[Joe4]

Getting rid of "Select" and Selection" statements can help speed up your code. Try this:

Sub FTSE()

    Dim lrv As Long, lrw As Long, lrx As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False

'   Find last row in column V with data
    lrv = Cells(Rows.Count, "V").End(xlUp).Row

    Range("V11:V" & lrv).FormulaArray = _
        "=IFERROR(INDEX(Ftse!C[-4],MATCH(MAX(IF(RC[-21]=Ftse!C[-20],Ftse!C[-16])),IF(RC[-21]=Ftse!C[-20],Ftse!C[-16]),0)),0)"

    Range("V11:V" & lrv).Value = Range("V11:V" & lrv).Value

'   Find last row in column W with data
    lrw = Cells(Rows.Count, "W").End(xlUp).Row

    Range("W11:W" & lrw).FormulaArray = _
        "=IFERROR(INDEX(Ftse!C[-15],MATCH(MAX(IF(RC[-22]=Ftse!C[-21],Ftse!C[-17])),IF(RC[-22]=Ftse!C[-21],Ftse!C[-17]),0)),0)"

    Range("W11:W" & lrw).Value = Range("W11:W" & lrw).Value
    
'   Find last row in column X with data
    lrx = Cells(Rows.Count, "X").End(xlUp).Row

    Range("X11:X" & lrx).FormulaArray = _
        "=INDEX(Positions!C[-22],MATCH([@[FundID]],Positions!C[-16],0))"

    Range("X11:X" & lrx).Value = Range("X11:X" & lrx).Value

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Note that you have to change the column you are using to determine the last row. If all three columns are supposed to end on the same line, then you really only need to find it once, and use that variable for all three.

 

[dlatha01]

Many thanks Joe, I'll give it a go