List Comparison to Array

[jstevens]

I am having a challenge comparing a list of items to an Array. There are certain items missing from the list that exist in the Array. My end result is to append these missing Array items to the list.

I have attached a sample workbook which contains VBA code that I have been using.

WL_Sample.xls

Thanks for taking a look,
John

[HansV]

You say in your workbook "The end result or list should contain the same items in the Array." Why not simply erase the list and dump the array into the worksheet?

[jstevens]

Hans,

I wish it were that simple. What I'm trying to do is compare the items in the array to what is listed. If an item does not exist in the list, append it to the bottom of the list.

Regards,
John

[HansV]

Do you have a particular reason for wanting to use arrays?

And where does the worksheet Send come into this? It's mentioned in the code but it's not present in the workbook.

[jstevens]

I capture a list similar to the one shown and place it in an Array (which is kept in memory). A new list is generated and then compared. Some items in the Array may not show up in the new list therefore appending to the list would be required.

The reference to the worksheet Send is my error (comes from a production workbook). It should reference Sheet1.

Regards,
John

[jstevens]

I came up with an alternate solution.

Code: Select all

    For r = LBound(myArray, 1) To UBound(myArray, 1)
        Range("A1").FormulaArray = "=MATCH(""" & myArray(r, 1) & myArray(r, 2) & """,A1:A50000&B1:B50000,0)"
            
        If IsError(Range("A1")) = True Then
            oLastRow = Worksheets("Send").Range("A65536").End(xlUp).Row + 1
            
            For oCol = 1 To UBound(myArray, 2)
                Select Case oCol
                
                Case 1, 2
                    Cells(oLastRow, oCol) = "'" & myArray(r, oCol)
                Case Else
                    Cells(oLastRow, oCol) = myArray(r, oCol)
                End Select
            Next oCol
            
        End If
    
    Next r

Hans thank you for taking a look,
John

[HansV]

I see that you correctly reversed the logic, it was what I would have suggested.