比较VB Excel中的二维数组 [英] Comparing 2-dimension arrays in VB 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 2 3 4）的示例行1将被视为与目标中的匹配（在第2行）。所以我需要比较源中给定行的目标中的每一行。如果源中的行不存在于目标中，那么我将需要记下一些如何标记为不存在于目标中。

（不是实际代码只是想法）：

 对于我到ubound（srcArray）
 isFound = False 
对于j到ubound（trgArray）
如果srcArray（i）= trgArray（j）然后
 isFound = True 
 
如果Not isFound然后
 //记下有些排序
  

阵列。但是试图在VB或其他方法的某种循环中为2d数组做这个。在Excel中不太熟悉VB。如果可能，我也想看看每一行是整个数组，而不是单独比较每个数组的每个元素。

解决方案

这里是一个如何循环和比较2D数组的元素的例子：

  Sub ArrayCompare（）
 Dim MyArr1作为变式，MyArr2作为变量，X长，Y为长
 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}] 
对于X = LBound（MyArr1）到UBound（MyArr1）
对于Y = LBound（MyArr1，1）To UBound（MyArr1，1）
如果MyArr1（X，Y）= MyArr2（X，Y）则MsgBox X& ：& Y& ：& MyArr1（X，Y）
下一个
下一个
 End Sub 
  

这是我更新的代码，以每行作为字符串进行比较（谢谢@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}] 
对于X = LBound（MyArr1）到UBound（MyArr1）
对于Y = LBound（MyArr2）到UBound（MyArr2）
如果Join（Application.Transpose（Application.Transpose（Application.Index（MyArr1，X，0））），|）= Join Application.Transpose（Application.Transpose（Application.Index（MyArr2，Y，0））），|）然后MsgBox在MyArr1索引找到匹配：& X& 和MyArr2索引：& Y 
下一个
下一个
 End Sub 
  

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.

解决方案

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

这篇关于比较VB Excel中的二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！