VBAのvariant型の配列で抽出すると2回目以降重複が出る

Question

vbaの配列に関する質問です。
シートにある2500行10列程度のデータを配列に入れて、条件で抽出をかけて書き出しをしています。ところが抽出2回目以降、表示されるデータが重複してしまいます。
例えば「卒業」という語句が含まれている行をすべて抽出したいと思った時に、1回目が3件抽出されると、2回目は同じものが下に表示され合計6件、3回目は9件という感じです。

配列の中でヒットしたら、同じ配列の先頭に差し替える、という流れの記述をしています。

Sub 抽出()
    Dim i As Long, n As Long, m As Long
    Dim buf As Variant
    Dim x As String, y As String, z As String
    Dim cnt As Long: cnt = 1
    Dim num As Long
    
    x = Range("j2")

    Range("a2:h100").ClearContents
    
    Set buf = Sheets("data").Range("a2:h3000")
    
    If x <> "" Then
        For i = 1 To 3000
        If buf(i, 2) Like "*" & x & "*" Then
            For n = 1 To 8
            buf(cnt, n) = buf(i, n)
            Next n
        cnt = cnt + 1
        End If
        Next i
    End If

    For m = 1 To cnt - 1
        For n = 1 To 8
        Cells(m + 1, n) = buf(m, n)
        Next n
    Next m
    
End Sub

原因と解決策をご教示いただけないでしょうか。

Answer 1

シートにある2500行10列程度のデータを配列に入れて、

まず、この認識が間違っています。

   Set buf = Sheets("data").Range("a2:h3000")

これでbufには配列が格納されると思っているのかもしれませんが、
bufにはRange（セル範囲）が格納されます。
Setを使うとオブジェクトの代入になりますので。
その後のループ内で、Range（セル範囲）を先頭から一致したデータで上書きしています。

            For n = 1 To 8
            buf(cnt, n) = buf(i, n)
            Next n

次にこの処理を実行すると上書きした一致データも抽出してしまうので、繰り返すと重複してしまいます。

buf(cnt, n) = buf(i, n)は一見配列への代入に見えますが、
buf.Cells(cnt, n).Value = buf.Cells(i, n).Valueの省略形です。
つまりSheets("data")のセルへの上書きになります。

bufへ配列として格納するコードは下記になります。

   buf = Sheets("data").Range("a2:h3000").Value

Setは使用しない。
.Valueを付ける。

最初のコードをこれに変更すると重複せずに抽出できると思います。

Comments

[Izumo1101]

大変助かりました。
いつからかsetを付けて違和感なくやっていましたが、言われてみれば確かに。。。
本当に勉強になりました！