vba 用excel VBA比较两个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16977174/
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
Comparing two Arrays with excel VBA
提问by user2462291
As for the problem, I need to be able to compare all data in Variant array A to all data in Variant array B. I know I need some kind of double loop (so that every A value is checked against all B values), but I can't figure out how to do it. Here's what I have so far:
至于问题，我需要能够将变体数组 A 中的所有数据与变体数组 B 中的所有数据进行比较。我知道我需要某种双循环（以便针对所有 B 值检查每个 A 值），但是我不知道该怎么做。这是我到目前为止所拥有的：
Sub Button_Click()
Dim trgtRange As Variant
Dim tempRange As Variant
Set myRange = ThisWorkbook.Sheets(1).Range("L:L")
For Each cell In myRange
If IsEmpty(cell) Then
ActiveCell.Offset(-1, 0).Select
currentRow = ActiveCell.Row
Set trgtRange = Range("L2:L" & currentRow)
Exit For
End If
Next cell
Set tempRange = Range("A1:A" & currentRow - 1)
' Insert a double loop here
End Sub
So, trgtRangeis the Variant A and tempRangeis Variant B. I know I could have set the Variant B up a little easier, but I already did it that way. After all, code should be polished as last operation anyway.
所以，trgtRange是变体 AtempRange还是变体 B。我知道我可以更容易地设置变体 B，但我已经这样做了。毕竟，代码无论如何都应该作为最后的操作进行打磨。
You might be wondering why Variants A and B are completely the same. Well, that's because I need to compare them so that I can find values that are close to each other, (i.e 10000 and 12000) and I need to incorporate some kind of tolerance for it.
您可能想知道为什么变体 A 和 B 完全相同。嗯，那是因为我需要比较它们，以便我可以找到彼此接近的值（即 10000 和 12000），并且我需要对其进行某种容忍。
回答by David
Here is my answer. Why do you need two loops to do this. Some relative addressing handles this issue quite nicely. Set up a spreadsheet like this for an example:
这是我的答案。为什么你需要两个循环来做到这一点。一些相对地址很好地处理了这个问题。例如，设置一个这样的电子表格：
and your code is simply this
你的代码就是这样
Sub Button_Click()
Dim dblTolerance As Double
Dim tmp As Range
'Get source range
Set tmp = ActiveSheet.Range("A2")
'Get tolerance from sheet or change this to an assignment to hard code it
dblTolerance = ActiveSheet.Range("D13")
'use the temporary variable to cycle through the first array
Do Until tmp.Value = ""
'Use absolute function to determine if you are within tolerance and if so put match in the column
'NOTE: Adjust the column offset (set to 4 here) to match whichever column you want result in
If Abs(tmp.Value - tmp.Offset(0, 2).Value) < dblTolerance Then
tmp.Offset(0, 4).Value = "Match"
Else
tmp.Offset(0, 4).Value = "No Match"
End If
'Go to the next row
Set tmp = tmp.Offset(1, 0)
Loop
'Clean up
Set tmp = Nothing
End Sub
The comments in the code explain how it works. This is superior to a double loop because relative referencing is faster, the memory use is more efficient and you only have to make one pass at each row.
代码中的注释解释了它是如何工作的。这优于双循环，因为相对引用更快，内存使用效率更高，并且您只需在每一行进行一次传递。
If you are required for some reason to use a double loop let me know, but that is inferior performance wise to this methodology. Hope this helps.
如果您出于某种原因需要使用双循环，请告诉我，但这与此方法相比性能较差。希望这可以帮助。
