Autofill formula until formula results blank "" Need To Speed up

Hello
I am trying to generate a report that references another workbook using the formula
=IFERROR(INDEX(!$H:$AO,SMALL(IF(!$AF:$AF=$D$2,ROW(!$H:$H),IF(!$AF:$AF=$D$3,ROW(!$H:$H))),ROW(!1:1)),1),"")
(File Location Removed For ease of viewing) (ctrl+shift+enter confirmed)

I am auto filling the formula down until the formula results "" and stop it. The number of rows will very each week thats where i looked into VBA.

I have used this formula in A5:F5 as well as H5:M5 to look up multiple values in workbook.

I'm pretty new to excel and VBA and think that there has to be a faster way to complete this task. (Excel 2010)

The VBA code I'm using is:

Please Login or Register  to view this content.

The issue i have is that this takes a really long time to complete it there a more efficient use of code that will make this faster.

i think that the main issue is with the loop

Please Login or Register  to view this content.

Having to calculate cell every time seems to take a lot of time but if i don't it will run indefinitely.

Thank you in advance for any help

First of all, your code does not need to select ranges to work with them. What I mean is:

Please Login or Register  to view this content.

is equivalent to

Please Login or Register  to view this content.

Not having to select ranges will speed up the code.

Another simple thing you can do is add Application.ScreenUpdating = False to the beginning of your code. This will turn off screen updating so that you will not be able to see the changes until the code has finished running. This vastly increases the speed. Turn it back on at the end of your code with Application.ScreenUpdating = True.

In this part you set a variable to determine the last row to autofill to and then autofill in one statement without a loop:

Please Login or Register  to view this content.

Here, you autofill each row one by one:

Please Login or Register  to view this content.

Can you not do the second autofill the same way?

No because the second auto fill is looking for another set of data.
i.e. A:F is looking for "Ready" and H:M is looking for "Complete"

Maybe I am missing something. I see neither words ("Ready" nor "Complete") referenced in your code.

The first autofill looks for the last value in the range. The second autofill loops until a blank row is reached. Those seem to be the same thing to me, no?

The first auto fill is populating down until the formula results "" (so that it stops after 300 line or whatever it may be)
The reason for the macro is so that i don't have unnecessary formulas in the workbook ie fill it down 2000 rows or more as it grows so that it can be opened relatively fast

The formulas in A5:F5 is looking for "Ready" and H5:M5 is looking for "Complete" they reference some other cells mostly if i need to make some additional search criteria