Compare 2 Arrays

Question

I am comparing two Arrays in VBA for Excel 2010. Here is my sample code:

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.

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.

Answer 1

Edit1: I've re-written your loop a bit which is the cause of the problem I think. Ubound and Lbound assumes 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 vArray3 should be Dimensioned. I didn't see it in your code. Also added a Boolean variable.

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