在VB Excel中比较二维数组(Comparing 2

在VB Excel中比较二维数组(Comparing 2-dimension arrays in VB Excel)

我正在尝试比较VBA Excel中的两个2​​d数组。

资源：

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 2 3 4）的第1行将被视为匹配，因为它将在目标（第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或其他方法中的某种循环中为2d数组执行此操作。 不太熟悉Excel中的VB。 如果可能的话，我还想将每一行视为整个数组，而不是单独比较每个数组的每个元素。

I'm trying to compare two 2d arrays in VBA Excel.

Source:

1 2 3 4

4 5 6 2

3 3 4 4

Target:

4 5 3 2

1 2 3 4

3 7 7 5

Given the above two 2-d arrays which I will call source and target I want to compare each row from source with entire target and check if it exists in target. For Example row 1 from source (1 2 3 4) would be considered a match as it would found in target (at row 2). So I need to compare each row in target for a given row from source. If row in source does not exist in target then I will need to make note of this some how in order to mark as not existing in target.

Something on the lines of (not actual code just idea):

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

I know approach worked ok for single dim. array. But trying to do this for 2d arrays in some sort of loop in VB or other method. Not too familiar with VB in Excel. I would also like to look at each row as entire array if possible rather than comparing each element for each array individually.

最满意答案

以下是如何循环和比较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

这是我更新的代码，将每行比较为字符串（Thanks @Tim Williams :)）：

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

Here is an example of how to loop and compare the elements of a 2D array:

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

Here is my updated code to compare each row as a string (Thanks @Tim Williams :)):

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