Hi forum,

I need to find equipment delivery dates that are relevant to me from a list provided by the expediting department. Their list varies in number of rows (equipment items) between around 22,000 to around 25,000 each week. I have a “lookup list” that contains part numbers that I need to track which is 4759 rows long.

The loop below takes values in column D (my lookup list, 4759 rows) and looks for matching values in Column A (from the expediting report). It then returns the delivery date from column B.

As I don’t have a fixed number of rows to deal with each week (because the expediting report changes) I have set my formula to 30,000 rows (R30000C1 in code below).

This works fine but takes about 5 minutes to execute.

Is there a way to change my code to always process only the number of rows present in column A? In other words, no matter how many items are in the expediting report each week (which I paste into columns A and B) my code will always process all entries, but no more. 

Range("F1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Counter = 2
    Do While IsEmpty(Cells(Counter, 4)) = False
        Cells(Counter, 6).FormulaArray = "=IF(SUMPRODUCT((R2C1:R30000C1=RC[-2])*(R2C2:R30000C2=""""))>0,"""",IF(MAX(IF(R2C1:R30000C1=RC[-2],R2C2:R30000C2,0))=0,"" "",MAX(IF(R2C1:R30000C1=RC[-2],R2C2:R30000C2,0))))"        
        Counter = Counter + 1        
    Loop
Thanks very much

Dave