☰

VBA を使用して 2 つの列の一致を比較する

tag-icon • tag-icon microsoft-excel vba macros
VBA を使用して 2 つの列の一致を比較する

次のタスクをより速く実行する方法について、誰かアイデアをお持ちでしたら、本当にありがたいです。列 A に名前のリストがあり、それらの名前のいずれかが列 C に表示されるかどうかを確認したいと考えています。以下のコードは、500 の名前でテストしたときにはうまく機能しました。しかし、列 A に約 3000 の値、列 C に 150000 の値がある完全なデータを使用すると、2 時間近く経過しても終了しないため、正しく実行されているかどうかわかりません。

Sub compare_cols122()

    Dim NameList As Worksheet
    Dim i As Long, j As Long
    Dim LastRow As Long

    Set NameList = Excel.Worksheets("Names")


    LastRow = NameList.UsedRange.Rows.Count

    Application.ScreenUpdating = False

    For i = 2 To LastRow
        For j = 2 To LastRow
            If NameList.Cells(i, 1).Value <> "" Then
                If InStr(1, NameList.Cells(j, 3).Value, NameList.Cells(i, 1).Value, vbTextCompare) > 0 Then
                    NameList.Cells(j, 3).Interior.ColorIndex = 6
                    NameList.Cells(i, 1).Interior.ColorIndex = 6
                    Exit For
                Else
                End If
            End If
        Next j
    Next i

Application.ScreenUpdating = True

End Sub

答え1

データを配列にロードし、それらとの比較を実行することで高速化されたコードを次に示します。

Sub compare_cols122()

    Dim NameList As Worksheet
    Dim i As Long, j As Long

    Set NameList = Excel.Worksheets("Names")

    Dim rngNames As Range
    Set rngNames = Range("A1", Range("A1").Offset(Rows.Count - 1).End(xlUp))
    Dim varNames As Variant
    varNames = rngNames.Value2

    Dim rngData As Range
    Set rngData = Range("C1", Range("C1").Offset(Rows.Count - 1).End(xlUp))
    Dim varData As Variant
    varData = rngData.Value2

    Application.ScreenUpdating = False

    For i = LBound(varNames) + 1 To UBound(varNames)
        For j = LBound(varData) + 1 To UBound(varData)
            If varNames(i, 1) <> "" Then
                If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    NameList.Cells(j, 3).Interior.ColorIndex = 6
                    NameList.Cells(i, 1).Interior.ColorIndex = 6
                    Exit For
                Else
                End If
            End If
        Next j
    Next i

    Application.ScreenUpdating = True

End Sub

内側のループと外側のループを入れ替えると、一致がどれほど稀であるかに関係なく、検索速度が低下するだけであることに注意してください。

答え2

セルからデータを何度も読み取るのは非効率的です。

配列を使用してすべての値を読み込みます。

次に、配列に対して必要な操作を実行します。

すべてが完了したら、値をセルに戻します。

関連情報