Speed up Macro

[Bars03]

Hi

I am using the below code to assist in creating a journal. It is assigning a Nominal code to use based on certain criteria. The problem I have is that there can be several thousand lines on the spreadsheet and it is proving very time consuming to run.

Is there any alternative code that I can use to speed up the process?

Thanks on Advance.



Sub UpdateTOrigBankAcc()
'
' UpdateTOrigBankAcc Macro
'

'

Application.ScreenUpdating = False


Dim Lastrow As String

Sheets("Transactions Orig").Select

Range("A1").Select
Selection.End(xlDown).Select
Lastrow = ActiveCell.Row


Range("M2").Select
Selection.FormulaArray = _
"=INDEX(Identifier!C[-8],MATCH(1,(Identifier!C[-12]='Transactions Orig'!RC[-12])*(Identifier!C[-11]='Transactions Orig'!RC[-9]),0))"

Range("M3").Select
Selection.Copy
Range("M2:M" & Lastrow).Select

ActiveSheet.Paste
Columns("M:M").Select
Selection.Copy
Range("M1").Select
Selection.PasteSpecial Paste:=xlPasteValues


Application.ScreenUpdating = True



End Sub

 

[Tubal]

Hi Bars03,

The code looks clean. I don't think the Macro is the slow part. I think it is the array formula in column M is the slow part. Here are some options that _might_ speed it up.
1) (re) arrange you data in "Identifier" so the equation is simpler
2) faster computer (right....)
3) It is a long shot, but look at reading "Transactions Orig" and "Identifier" into a VBA Array then process the results and past back the array. If you go this route, be sure to find and use the code to paste the entire array in one shot. If you do this cell by cell, you will find it to be very slow. The Excel functions run "fast" compared to VBA - given the equation / functions are simple. There is a point in time where a VBA function becomes faster then native Excel functions, but that break point is only validated through tests.

Tubal

 

[njimack]

You don't need to select sheets or cells to perform actions.

Sub UpdateTOrigBankAcc()

' UpdateTOrigBankAcc Macro




Application.ScreenUpdating = False


Dim Lastrow As String


With Sheets("Transactions Orig")


    Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row


    With .Range("M2:M" & Lastrow)
        .FormulaArray = "=INDEX(Identifier!C[-8],MATCH(1,(Identifier!C[-12]='Transactions Orig'!RC[-12])*(Identifier!C[-11]='Transactions Orig'!RC[-9]),0))"
        .Formula = .Value
    End With
End With




Application.ScreenUpdating = True






End Sub