optimizing match formula looping in vba

Question

Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 3 To Lastrow

       Sheets("sample").Range("AM1000000").End(xlUp).Offset(1, 0).Select
        Selection.FormulaArray = _
        "=IF(ISNUMBER(MATCH(1," & Chr(10) & "  (order!R2C15:R1000000C15=RC[-24])*" & Chr(10) & "  (order!R2C7:R1000000C7=RC[-32])*" & Chr(10) & "  (order!R2C24:R1000000C24=RC[-15])," & Chr(10) & "  0)), ""pass"",""review"")"

    Next i
      Columns("AM:AM").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I am using match formula inside of for loop, but it is taking too much time. Is there any way to optimize this code to make it go faster?

Thank you!

score 0 · Accepted Answer · answered May 16 '17 at 18:03

There are quite a few improvements you could make, a couple of main ones are:

1) Reading and writing to cells on a worksheet is very slow, as are string manipulations. Instead work out the result first then write it to the sheet rather than write a formula to the cell. Better yet, store the results in an array and write them all out at the end (but that is beyond the scope of this question, you can search about arrays online). You can use application.worksheetfunction to recreate your existing formulas in VBA, or you might consider creating your own function to do it.

2) Avoid selecting cells, again - a very slow operation. Instead use your for loop with the Cells reference to specify the affected cell:

For i = 3 To Lastrow

       Sheets("sample").cells(i, 39).FormulaArray = _
        "=IF(ISNUMBER(MATCH(1," & Chr(10) & "  (order!R2C15:R1000000C15=RC[-24])*" & Chr(10) & "  (order!R2C7:R1000000C7=RC[-32])*" & Chr(10) & "  (order!R2C24:R1000000C24=RC[-15])," & Chr(10) & "  0)), ""pass"",""review"")"

Next i

The Cells syntax is (row, column). Here i is the row number, 39 is column AM.

score 0 · Answer 2 · answered May 16 '17 at 18:04

By lessening the reference range in the array formulas we can speed it up.

Also removing the clipboard will also speed it up:

lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 3 To lastrow
    With WorksSheets("sample").Range("AM" & i)
        .FormulaArray = _
            "=IF(ISNUMBER(MATCH(1," & Chr(10) & "  (order!R2C15:R" & lastrow & "C15=RC[-24])*" & Chr(10) & "  (order!R2C7:R" & lastrow & "C7=RC[-32])*" & Chr(10) & "  (order!R2C24:R" & lastrow & "C24=RC[-15])," & Chr(10) & "  0)), ""pass"",""review"")"
        .Value = .Value
    End With

optimizing match formula looping in vba

2 Answers2