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.
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