match formula in for loop OPTIMISATION

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
Code:
 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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Code:
Dim rng As Range
Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
With Sheets("Sample")
    Set rng = .Range(.Range("AM1000000").End(xlUp).Offset(1, 0), .Range("AM" & Lastrow-2))
End With
rng.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"")"
rng = rng.Value

Re this line :
Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Are you looking for the last row containing data?
If so, this is not a good way - it might return a row far beyond the last data row.
Is there a column that could be used to find the last data row?
 
Last edited:
Upvote 0
Thanks for the reply, but this code gives me only result for the first line. Is there any way to fix this?

Thanks!
 
Upvote 0
Cannot say without access to your workbook.
Step through via F8 to see what each line is doing and what is being assigned to the variables.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top