Match formula for loop in VBA

Question

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

 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

• PeterT: What is the end result you're trying to achieve? I suspect there is a much easier way to accomplish a solution

Answer 1

The key optimisation can be found in this part:

Dim orderColumnOArray as Variant, orderColumnGArray as Variant, orderColumnXArray as Variant
orderColumnOArray = ThisWorkbook.Worksheets("order").Range("O2:O1000000").Value
orderColumnGArray = ThisWorkbook.Worksheets("order").Range("G2:G1000000").Value
orderColumnXArray = ThisWorkbook.Worksheets("order").Range("X2:X1000000").Value

There above, we are using arrays to store the order data, and we can traverse them more efficiently, rather than repeat a full array formula calculation for each cell.

Note that even then we could cut down on the million rows, and do a lastRow calculation for the order sheet. Perhaps that will bring great improvement too. However if we say for now that you will genuinely have that number of data rows in the order Worksheet, we can still go about it a bit better.

With ThisWorkbook.WorkSheets("sample")
 'I will assume that the column needs to filled from AM3 to AMLastrow
 'I also assume the tested values are strings or integers, not dates or floats
 Dim resultArray() as String
 ReDim resultArray(3 to Lastrow)
 Dim j as Long
 For i = 3 To Lastrow
 resultArray(i) = "review"
 For j = 1 To Ubound(orderColumnOArray)
 'The below nested Ifs can be done in many different ways
 'e.g. And with the 3 equality tests
 'or even by initially making two concatenated arrays!
 If .Range("O" & i).Value = orderColumnOArray(j, 1) Then
 If .Range("G" & i).Value = orderColumnGArray(j, 1) Then
 If .Range("X" & i).Value = orderColumnXArray(j, 1) Then
 resultArray(i) = "pass"
 Exit For
 End if
 End if
 End if
 Next j
 Next i
 'Now we can write back the array to the worksheet faster
 .Range("AM3").Resize(Lastrow - 3 + 1, 1).Value = resultArray
End With

Answer 2

May want to read the cells into an array, process within the VBA world, then write out cells to the sheet. Going back and forth to the worksheets typically slow.