Доброе время суток.
Kuzmich, а почему не на словаре? На пятистах фамилиях 2,6 секунды поиском против 0,008 на словаре.
Код |
---|
Public Sub InsertFirstEntry()
Dim lRow As Long, vData As Variant, sKey As String
Dim i As Long, pDict As Object, vOut() As Variant, t As Single
t = Timer
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
vData = ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(lRow, 2)).Value
ReDim vOut(1 To UBound(vData), 1 To 1)
Set pDict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(vData)
sKey = vData(i, 1)
If Not pDict.Exists(sKey) Then
pDict(sKey) = vData(i, 2)
End If
vOut(i, 1) = pDict(sKey)
Next
ActiveSheet.Range("C2").Resize(UBound(vOut), 1).Value = vOut
Debug.Print Timer - t
End Sub |