Results 1 to 8 of 8

Difference between two 2D Arrays

Threaded View

  1. #1
    Registered User
    Join Date
    11-23-2020
    Location
    Kyoto
    MS-Off Ver
    Microsoft Office Standard 2013
    Posts
    6

    Difference between two 2D Arrays

    I am trying to get a difference of two arrays as a third array.

    Here is a sample of what I am trying to do:

    sample.png

    So far I got this:

    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
    But I am getting a type mismatch error on this line:
    coll.Add arr1(i, j), arr1(i, j)
    Any one know what can be wrong? Or even if I am correct with the code so far?
    Attached Files Attached Files
    Last edited by Pimo; 11-24-2020 at 01:01 AM. Reason: solved

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Querying Large Series of Arrays And Displaying Unique Values in Arrays [Hard Index Match?]
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2020, 04:52 PM
  2. Replies: 1
    Last Post: 07-29-2014, 08:26 AM
  3. Declaring multiple multi-dimensional arrays (jagged arrays) - compile error?
    By dfribush in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-20-2013, 05:06 PM
  4. Get the Difference between Two 2 D Arrays in VBA
    By pavanlalit in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-07-2013, 06:03 PM
  5. [SOLVED] New to arrays-where do I find a good beginners guide to multi dimensional arrays
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-04-2013, 07:44 PM
  6. [SOLVED] Need to count if the difference in values between two arrays is less than 5
    By italianiceman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-07-2012, 03:08 PM
  7. [SOLVED] VBA - averaging the difference of 2 arrays, ignoring blanks
    By RobPaolillo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-08-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1