The smallest value in column based on criteria

I got a code that is working OK at the moment. The problem is that this code is super slow. I was wondering if there might be a more efficient way to solve this problem.
I got two different workbooks (wb1 and wb2) with one sheet each (ws1 and ws2). The first workbook (wb1) which is my main workbook is about 7000 rows with 22 columns.
My second workbook (wb2) is instead 350000+ rows with 28 columns.

What do I want to do with my code? In my main document (wb1) then I want to get the lowest value in wb2 based on a criteria in column 5 (wb1). In other terms, its like =min(if(..)) in a excel array formula. Is there anyway that I can make this more efficient?

Please Login or Register  to view this content.

Thanks for your answer sintek!

The screenupdating part is already build into my code, but forgot to add those, but thanks anyway.
Going with elseif instead of else and than if is a upgrade, so thank you. But my code is still too slow, since its so much looping.

Originally Posted by sintek

Hi Gandreso

Try this:

Please Login or Register  to view this content.

To make it clear what I want to do;

1. Column 5 in wb1 is my criteria, might look like this 5181833.
2. Find the lowest value in column 15 in wb2 that has my criteria (5181833) in column 5.
3. Add the lowest value in column 18 in wb1, lowest value might look like this 201716.
4. Go next row (criteria) in wb1

I like your idea, its smart! Im gonna try it out, it might be a lot better. I will get back to you with some results.

Originally Posted by Kaper

So basically, you look for a minimum value in column 15 for each value in column 5 (both activesheet)
May be it will be quicker to start with sorting activesheet by two keys - parameter as primary key (column 5) and values (column 15) (order does not matter - may be ascending) as second key - value - column 5 (here obviously ascending orger.
now if you go from the top the value (column 5) for a first occurence of given parameter (column 15) is the minimum, and you could use it for filling column 18 in wb1.Sheets(ws1)
It could be even easier if output sheet is also sorted (one key here - just parameter - column 5 - sorted the same order as in activesheet)
Sort is a very quick and effective method and your dataset is quite big. so it may be a berrer idea than dictionary object.

May be the text above is not super-clear, buy for sure it would be easier to show it in the macro working on a dummy data in attachment than just to write anb describe