Pimo
11-23-2020, 09:01 PM
Hi!
I am trying to get the difference of two 2D Arrays in VBA. Please look at the sample workbook
I got the code that I thought will be working well but I am getting a type mismatch error.... on this line: coll.Add arr1(i, j), arr1(i, j)
Here is my code so far:
Sub Test()
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim coll As Collection
Dim i As Long, j As Long
With Worksheets("Sheet2")
LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
arr1 = .Range("A1:C" & LastRowColumnA).Value
End With
With Worksheets("Sheet1")
LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
arr2 = .Range("A1:C" & LastRowColumnA).Value
End With
Set coll = New Collection
For i = LBound(arr1, 1) To UBound(arr1, 1)
For j = LBound(arr1, 2) To UBound(arr1, 2)
coll.Add arr1(i, j), arr1(i, j)
Next j
Next i
For i = LBound(arr2, 1) To UBound(arr2, 1)
For j = LBound(arr2, 2) To UBound(arr2, 2)
On Error Resume Next
coll.Add arr2(i, j), arr2(i, j)
If Err.Number <> 0 Then
coll.Remove arr2(i, j)
End If
On Error GoTo 0
Next j
Next i
ReDim arr3(1 To coll.Count, 1 To 1)
For i = 1 To coll.Count
arr3(i, 1) = coll(i)
Debug.Print arr3(i, 1)
Next i
Worksheets("Sheet2").Range("F1").Resize(UBound(arr3, 1), 1).Value = arr3 End Sub
Does anyone knows how it can be solved?
27493
I am trying to get the difference of two 2D Arrays in VBA. Please look at the sample workbook
I got the code that I thought will be working well but I am getting a type mismatch error.... on this line: coll.Add arr1(i, j), arr1(i, j)
Here is my code so far:
Sub Test()
Dim arr1 As Variant
Dim arr2 As Variant
Dim arr3 As Variant
Dim coll As Collection
Dim i As Long, j As Long
With Worksheets("Sheet2")
LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
arr1 = .Range("A1:C" & LastRowColumnA).Value
End With
With Worksheets("Sheet1")
LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row
arr2 = .Range("A1:C" & LastRowColumnA).Value
End With
Set coll = New Collection
For i = LBound(arr1, 1) To UBound(arr1, 1)
For j = LBound(arr1, 2) To UBound(arr1, 2)
coll.Add arr1(i, j), arr1(i, j)
Next j
Next i
For i = LBound(arr2, 1) To UBound(arr2, 1)
For j = LBound(arr2, 2) To UBound(arr2, 2)
On Error Resume Next
coll.Add arr2(i, j), arr2(i, j)
If Err.Number <> 0 Then
coll.Remove arr2(i, j)
End If
On Error GoTo 0
Next j
Next i
ReDim arr3(1 To coll.Count, 1 To 1)
For i = 1 To coll.Count
arr3(i, 1) = coll(i)
Debug.Print arr3(i, 1)
Next i
Worksheets("Sheet2").Range("F1").Resize(UBound(arr3, 1), 1).Value = arr3 End Sub
Does anyone knows how it can be solved?
27493