Comparing two arrays with excel VBA

Question

As for the problem, I need to be able to compare all the data in the Variant A array with all the data in the Variant B array. I know that I need some kind of double loop (so that every value of A is checked for all values ​​of B), but I don't I can understand how to do it. Here is what I still have:

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 trgtRange is option A, and tempRange is option B. I know that I could change option B a bit, but I already did it that way. In the end, the code should be polished as the last operation.

You may be wondering why Options A and B are exactly the same. Well, this is because I need to compare them so that I can find values ​​close to each other (for example, 10000 and 12000), and I need to include some tolerance in it.

Answer 1

Here is my answer. Why do you need two loops. Some relative addressing does this pretty well. Set up the table, for example:

and your code is just that

 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 

Comments in the code explain how this works. This is superior to the double loop because relative reference is faster, memory usage is more efficient, and you only need to do one pass per line.

If for some reason you need to use a double loop, let me know, but this is worse compared to this methodology. Hope this helps.