It is a very good thing that you achieved a solution yourself, but some remarks.
Avoid Sheet interaction (reading - writing) as much as possible certainly for large numbers of data.
Avoid using Worksheetfunctions if there is a VBA substitute.
I tested your code on 1000 numbers to lookup in a 1000 numbers which your code took 0.14sec. while the code below only took 0.04sec
This might seem trivial but it's a gain of 75%
Code:
Sub tst3()
t = Timer
Dim sq, result
sn = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
ReDim sq(1 To UBound(sn))
For i = 1 To UBound(sn)
result = Application.Match(sn(i, 1), Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls") _
.Sheets("Weekly Shipped Details").Range("I5:I1000"), 0)
sq(i) = IIf(Not IsError(result), sn(i, 1), "#N/A")
Next
Range("C2").Resize(UBound(sn)) = WorksheetFunction.Transpose(sq)
MsgBox Timer - t
End Sub
Bookmarks