vba 比较 2 个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26457432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare 2 Arrays
提问by user2036624
I am comparing two Arrays in VBA for Excel 2010. Here is my sample code:
我正在比较 Excel 2010 中 VBA 中的两个数组。这是我的示例代码：
Dim vArray1 As Variant
Dim vArray2 As Variant
Set wb1 = ActiveWorkbook
Set myTable = wb1.Worksheets(3).ListObjects("Table3")
vArray1 = myTable.DataBodyRange
vArray2 = wb1.Worksheets(2).Range("B1:B" & lRow1).Value
k = 1
For i = LBound(vArray1) To UBound(vArray1)
For j = LBound(vArray2) To UBound(vArray2)
If vArray1(i, 1) = vArray2(j, 1) Then
' Do nothing
Else
vArray3(k, 1) = vArray1(i, 1)
k = k + 1
End If
Next
Next
I want to do a comparison of Column 1 in Table 3 with the range stored in vArray2.
我想将表 3 中的第 1 列与存储在 vArray2 中的范围进行比较。
Any value that is present in vArray1 but not present in vArray2 needs to be stored in vArray3. Unfortunately, I am cannot get this done. Any assistance would be appreciated.
vArray1 中存在但 vArray2 中不存在的任何值都需要存储在 vArray3 中。不幸的是，我无法完成这项工作。任何援助将不胜感激。
回答by L42
Edit1:I've re-written your loop a bit which is the cause of the problem I think. Uboundand Lboundassumes the first dimension if it is not supplied. So the way you do it and below should return the correct upper and lower bounds. But of course, it is better to be explicit when you're dealing with 2D arrays. Also vArray3should be Dimensioned. I didn't see it in your code. Also added a Booleanvariable.
编辑 1：我重新编写了您的循环，这是我认为问题的原因。如果未提供，则Ubound和Lbound假定第一个维度。所以你这样做的方式和下面应该返回正确的上限和下限。但是当然，在处理二维数组时最好是明确的。此外vArray3应该是尺寸。我没有在你的代码中看到它。还添加了一个布尔变量。
ReDim vArray3 (1 to 10, 1 to 2) '~~> change to suit
Dim dup As Boolean: k = 1
For i = LBound(vArray1, 1) To UBound(vArray1, 1) '~~> specify dimension
dup = False
For j = LBound(vArray2, 1) To UBound(vArray2, 1) '~~> specify dimension
If vArray1(i, 1) = vArray2(j, 1) Then
dup = True: Exit For
End If
Next j
If Not dup Then '~~> transfer if not duplicate
vArray3(k, 1) = vArray1(i, 1)
k = k + 1
End If
Next I
Or you can use match like this:
或者你可以像这样使用匹配：
'~~> Use 1D array instead by using Transpose
vArray2 = Application.Transpose(wb1.Worksheets(2).Range("B1:B" & lRow1))
For i = LBound(vArray1, 1) To UBound(vArray1, 1) '~~> specify dimension
If IsError(Application.Match(vArray1(i, 1), vArray2, 0)) Then
vArray3(k, 1) = vArray1(i, 1)
k = k + 1
End If
Next i
