excel - VBA:比较两个范围的更快方法？

Question

我需要比较两个范围，看看一个范围内的值是否出现在另一个范围内。这是我使用的代码:

Dim rng1 As Range
Dim rng2 As Range
Dim cell as Range
Dim found as Range

set rng1 = ....
set rng2 = ....
for each cell in rng1
set found = rng2.Find(what:=cell,.....
Next cell

如果范围是数千行，单列，则此代码是可以的。当涉及到数万时，它非常缓慢。

无论如何要加快速度？

Answer 1

这可能是处理大量数据的最快方式:

Option Explicit
Sub Test()

    Dim rng1 As Range
    Set rng1 = YourShorterRange

    Dim rng2 As Range
    Set rng2 = YourLargerRange

    Dim C As Range
    Dim Matches As Object: Set Matches = CreateObject("Scripting.Dictionary")
    'input the larger data inside a dictionary
    For Each C In rng2
        If Not Matches.Exists(C.Value) Then Matches.Add C.Value, 1
    Next C

    Dim i As Long
    Dim arr As Variant
    'input the shorter data inside an array
    arr = rng1.Value
    For i = 1 To UBound(arr)
        If Matches.Exists(arr(i, 1)) Then
            'your code if the value is found
        End If
    Next i

End Sub

为多里安编辑:

Option Explicit
Sub Test()

    Dim rng1 As Range
    Set rng1 = YourShorterRange

    Dim rng2 As Range
    Set rng2 = YourLargerRange

    Dim i As Long, j As Long
    Dim arr As Variant
    Dim Matches As Object: Set Matches = CreateObject("Scripting.Dictionary")
    arr = rng1.Value
    'input the larger data inside a dictionary
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Not Matches.Exists(arr(i, j)) Then Matches.Add arr(i, j), 1
        Next j
    Next i

    'input the shorter data inside an array
    arr = rng2.Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Matches.Exists(arr(i, j)) Then
                'your code if the value is found
            End If
        Next j
    Next i

End Sub