VBAで、2つの配列が完全一致するかどうかを調べる方法

Question

シート1のB1からB500まで縦方向に入力されたデータがあります。
そして、シート2のB4からB22まで縦方向入力されたデータがあります。

このとき、シート1のデータは、シート2のデータを部分集合として含むかどうかを判定したくて、
(つまり、大きい方の配列が、小さい方の配列を部分集合として含むかどうかを判定したくて)
以下のようなコードを書いたのですが、
「型が一致しません」というエラーになりました。

    For i = 1 To 500
        If Range(Sheets("シート1").Cells(i, 2), Sheets("シート1").Cells(i + 19, 2)).Value = Range(Sheets("シート2").Cells(4, 2), Sheets("シート2").Cells(4 + 19, 2)).Value Then
        MsgBox "含む"
        End If
    Next

どのようにすれば、うまくできるでしょうか?

Comments

[imihito]

1. +19は何を意味していますか？(4+19は23なのでB4:B22の範囲外ですし)
2. 順番を含めて一致している必要がありますか？それとも値さえ含まれていればいいですか？

[koridentetsu]

1.+19ではなく+18の誤りでした(手元で18に直した物で実験してみたのですが、結果は同じエラーです)。
2.順番を含めて一致している必要があります。

例えば、
Sheets("シートA").Cells(1,1), Sheets("シートA").Cells(10,1)).Value = Range(Sheets("シートB").Cells(1,1), Sheets("シートB").Cells(10,1)).Value
というコードだと、シートBのA1:A10が、まるごとそのままシートAのA1:A10にコピーできますが、
このようにRangeどうしが等しいかどうかをこの方法で比較しようとするとエラーになってしまうのでしょうか…?

Answer 1

Excelの仕様として、セル範囲を示すRangeに対してValueプロパティを取得すると、2次元のVariant型配列になります。
つまり、Range(Sheets("シート1").Cells(i, 2), Sheets("シート1").Cells(i + 19, 2)).Valueなどを評価した値はVariant型配列です。

そしてVBAの仕様として、配列に対して=などの比較演算を行うことができません。

そのため、ループなどを使用して比較していくのが一般的な方法だと思われます。

---

• 比較したいセル範囲がともに1行 or 1列である
• 値の比較は文字列として評価しても問題がない

上記の条件が満たされるのであれば、セルの値を文字列として結合し、
大きいほうの配列を文字列化したものの中に、小さいほうの配列を文字列化したものが含まれるかどうか
で判定することもできます。

こちらを処理にすると以下のようなイメージになります。

Sub teratail228184()
    'https://teratail.com/questions/228184
    
    '対象のシートが属しているブック
    Dim targetBook As Excel.Workbook
    Set targetBook = ThisWorkbook
    
    '大きい方のデータ
    Dim wholeSheet As Excel.Worksheet
    Set wholeSheet = targetBook.Worksheets.Item("シート1")
    Dim wholeData As Excel.Range
    Set wholeData = wholeSheet.Range("B1:B500")
    
    '1列のセル範囲であれば、ワークシート関数のTRANSPOSEで1次元配列に変換でき
    '1次元配列であればJoinで結合できる、
    '今回は手動操作ではセルへ入力するのが難しいタブ文字で結合
        'VBA TRANSPOSE関数で一次元データのみの二次元配列を一次元配列に変換する。
        'https://thom.hateblo.jp/entry/2018/08/02/073503
    Dim wholeDataCombined As String
    wholeDataCombined = VBA.Strings.Join( _
        WorksheetFunction.Transpose(wholeData), _
        vbTab _
    )
    
    
    '小さい方のデータ
    Dim flagmentSheet As Excel.Worksheet
    Set flagmentSheet = targetBook.Worksheets.Item("シート2")
    Dim flagmentData As Excel.Range
    Set flagmentData = flagmentSheet.Range("B4:B22")
    
    Dim flagmentDataCombined As String
    flagmentDataCombined = VBA.Strings.Join( _
        WorksheetFunction.Transpose(flagmentData), _
        vbTab _
    )
    
    
    If VBA.Strings.InStr(1, wholeDataCombined, flagmentDataCombined) <> 0 Then
        MsgBox "含む"
    Else
        MsgBox "含まない"
    End If
    
End Sub

参考

VBA TRANSPOSE関数で一次元データのみの二次元配列を一次元配列に変換する。 - t-hom’s diary

Answer 2

いろいろと方法は考えられると思いますが、一次元配列のようですので、一旦両方の配列ともに

• 文字列の配列に変換してJoinで結合
• セル範囲であれば、「Application.WorksheetFunction.concatenate」(concatが使えればそちらでも)で結合

を行って、文字列としてLIKEで比較してしまうのはいかがでしょうか。
手元にOfficeがないので、検証ができませんが・・・

Answer 3

エラー（型が一致しません）についてです。

下記のように一度複数セルの値を配列に入れるのが良いかと思います。

Dim a As Variant
a = Range(Cells(1, 1), Cells(2, 2)).Value

その後ループ処理をされてはどうでしょうか？