Slow Execution of a Loop

Hi guys,

I have built a user form that enables product search, I have Googled and found a simple enough form and apply like for like to my sheet. The problem is, it runs very very slow on my workbook. Initially, my thought was the number of products in the stock list, so I mirrored the product table into the sample file. It makes no real difference in terms of speed for the sample file to return the results instantly (257 rows of products) but mine is still extremely slow!

Below is the original code from the sample file which runs lightning speed even without any of the disabling event, screen updating etc.

Please Login or Register  to view this content.

This is my code

Please Login or Register  to view this content.

How much slower is based on how many results it returns. So if I search for a product that is unique, the loop takes around 0.64 sec, if I search keywords that returns 5 results, the loop will take 3.27 seconds. Compare to the template I have downloaded, which only takes less than a second regardless of how many results returned. I know my worksheet is bigger compared to the sample excel file but as I have disabled all events screen updating and formula updates, why it take so long to run that piece of code. Your help is greatly appreciated!

As WideBoy states above it is difficult to do anything without a working sample file. When i looked at the code the only thing i can spot that will slow it down is the amount of times it writes to the spreadsheet.

It is far fromperfect but the below is (roughly) where you could go to with it, storing the values in a variant and then write the array to the destinatiion sheet at the end.

Please Login or Register  to view this content.

Originally Posted by CheeseSandwich

As WideBoy states above it is difficult to do anything without a working sample file. When i looked at the code the only thing i can spot that will slow it down is the amount of times it writes to the spreadsheet.

It is far fromperfect but the below is (roughly) where you could go to with it, storing the values in a variant and then write the array to the destinatiion sheet at the end.

Please Login or Register  to view this content.

Hi CheeseSandwich,

Thank you so much for amending the code. I run the search and a runtime error 9 occurred and highlighted the below line

Please Login or Register  to view this content.

Thank you WBD,

I found the problem after deleting sheet by sheet to find out where is dragging it slow. I use a picture lookup which will put a picture into a cell base on another cell value. This requires a defined name and range which I have below.


=INDEX('Sample Template'!$M$1:$M$2,MATCH('Sample Template'!$A$1,'Sample Template'!$L$1:$L$2,0))


After deleting the above-defined name, the result is now almost instant. Not sure why this will cause the issue. I still wanted to use the picture lookup but am not sure how to remove the impact to my UserForm. Thank you!