Comparing two Arrays with excel VBA

Question

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:

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 the Variant A and tempRange is 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.

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.

I don't know why this questions was bumbed, but it seems like a poor choice. First, it's over four years old, second it is poorly framed and needs clarification, but third, this user hasn't been seen in four years either, so there little chance of getting the needed clarification, let alone it being unlikely that they still need an answer. — RBarryYoung
– RBarryYoung, Commented Oct 21, 2017 at 20:37

David · Accepted Answer · 2014-08-07 18:45:05Z

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:

Spreadsheet Layout

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.

It would be helpful to know what exactly is wrong with this answer. I asked if you were required to use a double loop so I could help.

Collectives™ on Stack Overflow

Comparing two Arrays with excel VBA

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related