Do-While loop within a do-while loop? Dealing with changing number of rows

[Motox]

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.

Please Login or Register  to view this content.

Thanks very much

Dave

[DGagnon]

you could use something like this that will identify the last row, and modify your formula accordingly

Please Login or Register  to view this content.

[Motox]

Thanks DGagnon,

I tried the code shown below and it starts returning values but hangs up after a while. When I debug it highlights the "Counter = Counter + 1" part of the code. Not sure why?


Sub TestIt()


Dim Counter As Long

Dim MaxCells As Long



Range("F1").Select


ActiveCell.FormulaR1C1 = "Date"
Counter = 2
MaxCells = Range("A40000").End(xlUp).Row
Do While IsEmpty(Cells(Counter, 4)) = False
Cells(Counter, 6).FormulaArray = "=IF(SUMPRODUCT((R2C1:R" & MaxCells & "C1=RC[-2])*(R2C2:R" & MaxCells & "C2=""""))>0,"""",IF(MAX(IF(R2C1:R" & MaxCells & "C1=RC[-2],R2C2:R" & MaxCells & "C2,0))=0,"" "",MAX(IF(R2C1:R" & MaxCells & "C1=RC[-2],R2C2:R" & MaxCells & "C2,0))))"

Counter = Counter + 1

Loop


End Sub

Thanks

Dave

[DGagnon]

try changing your while condition

Please Login or Register  to view this content.