2维数组中VB Excel中比较(Comparing 2-dimension arrays in V

我想在VBA Excel来比较两个二维数组。

资源：

1 2 3 4

4 5 6 2

3 3 4 4

目标：

4 5 3 2

1 2 3 4

3 7 7 5

鉴于上述两个2 d阵列我将称之为源和目标我想比较从与整个目标源的每一行，并检查它是否在目标存在。对于实施例1行从源（1 2 3 4）将被认为是，因为这将在目标实测值（第2行）的匹配。所以，我需要每一行的目标比较从源代码给定行。如果行源不存在目标，然后我将需要记下这一些如何以尽可能标志着目标不存在。

东西上（而不是实际的代码只是想法）行：

For i to ubound(srcArray)
    isFound = False
    For j To ubound(trgArray)
        If srcArray(i) = trgArray(j) Then
            isFound = True

    If Not isFound Then
        //make note of some sort

我知道的方法为单昏暗的工作确定。阵列。但是，试图为在VB或其他方法某种循环的二维数组做到这一点。不太熟悉，在Excel VB。我也想看看每个行作为整个数组如果可能的话，而不是每个元素为每个阵列单独进行比较。

Answer 1:

下面是一个例子如何循环和比较2D阵列的元素：

Sub ArrayCompare()
Dim MyArr1 As Variant, MyArr2 As Variant, X as long, Y as long
MyArr1 = [{1,2,3,4;4,5,6,2;3,3,4,4}]: MyArr2 = [{4,5,3,2;1,2,3,4;3,7,7,5}]
For X = LBound(MyArr1) To UBound(MyArr1)
    For Y = LBound(MyArr1, 1) To UBound(MyArr1, 1)
        If MyArr1(X, Y) = MyArr2(X, Y) Then MsgBox X & ":" & Y & ":" & MyArr1(X, Y)
    Next
Next
End Sub

这是我更新的代码，以比较每列作为字符串（感谢@Tim威廉姆斯:)）：

Sub ArrayCompare()
Dim MyArr1 As Variant, MyArr2 As Variant, X As Long, Y As Long
MyArr1 = [{1,2,3,4;4,5,6,2;3,3,4,4}]: MyArr2 = [{4,5,3,2;1,2,3,4;3,7,7,5}]
For X = LBound(MyArr1) To UBound(MyArr1)
    For Y = LBound(MyArr2) To UBound(MyArr2)
        If Join(Application.Transpose(Application.Transpose(Application.Index(MyArr1, X, 0))), "|") = Join(Application.Transpose(Application.Transpose(Application.Index(MyArr2, Y, 0))), "|") Then MsgBox "Found a match at MyArr1 index:" & X & " and MyArr2 index:" & Y
    Next
Next
End Sub

Answer 2:

如果你真的想避免环路，那么你用这种方式来提取单个“行”出你的2-D阵列出于比较的目的，但它可能会更快循环。

Sub Tester()

    Dim arr, rw

    arr = Range("A1:J10").Value 'get 2-d array from worksheet

    'get a 1-d array "row" out of the 2-d array
    rw = Application.Transpose( _
         Application.Transpose(Application.Index(arr, 1, 0)))

    'then you can (eg) create a string for comparison purposes
    Debug.Print Join(rw, Chr(0))

End Sub

文章来源: Comparing 2-dimension arrays in VB Excel