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?

Dim wb1 As Workbook, wb2 as Workbook
Dim ws1, ws2

Dim vDataE As Variant, vDataO As Variant
Dim vE As Variant, vR As Variant, v As Variant, i As Long

vDataE = ActiveSheet.UsedRange.Columns(5).Value
vDataO = ActiveSheet.UsedRange.Columns(15).Value


With wb1.Sheets(ws1).Range("A1").CurrentRegion

    vE = .Columns(5).Value
    vR = .Columns(18).Value
    
    With CreateObject("Scripting.Dictionary")
    
        For i = 2 To UBound(vDataE, 1)
            If Not .exists(vDataE(i, 1)) Then
                .Item(vDataE(i, 1)) = vDataO(i, 1)
            Else
                If vDataO(i, 1) < .Item(vDataE(i, 1)) Then
                    .Item(vDataE(i, 1)) = vDataO(i, 1)
                End If
            End If
        Next
    
        For i = 2 To UBound(vE, 1)
            If .exists(vE(i, 1)) Then vR(i, 1) = .Item(vE(i, 1))
        Next i
    
    End With
    
    .Columns(18).Value = vR
    
End With

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:

Option Explicit
Sub Test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim vDataE As Variant, vDataO As Variant
Dim vE As Variant, vR As Variant, v As Variant, i As Long
Application.ScreenUpdating = False
vDataE = ActiveSheet.UsedRange.Columns(5).Value
vDataO = ActiveSheet.UsedRange.Columns(15).Value

With wb1.ws1.Range("A1").CurrentRegion
    vE = .Columns(5).Value
    vR = .Columns(18).Value
    With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(vDataE, 1)
        If Not .exists(vDataE(i, 1)) Then
            .Item(vDataE(i, 1)) = vDataO(i, 1)
        ElseIf vDataO(i, 1) < .Item(vDataE(i, 1)) Then
            .Item(vDataE(i, 1)) = vDataO(i, 1)
        End If
    Next
    For i = 2 To UBound(vE, 1)
        If .exists(vE(i, 1)) Then
            vR(i, 1) = .Item(vE(i, 1))
        End If
    Next i
    End With
    .Columns(18).Value = vR
End With

Application.ScreenUpdating = True
End Sub

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