Compare 2 different arrays, identify the unique values and copy the values in cell

[kumarcoolz]

I am working on a excel with VBA. So my intension was to use array function to Compare 2 different arrays, identify the unique values and copy the values in cell (sheet2). This is the first time I am using arrays. I wanted to try out with Arrays as I have been told arrays work faster but I have not idea on arrays


Thanks to google. I have some progress in populating the 2 Arrays.
Array NewArr is the biggest array according to this code.
Array Arc is the list that is present in Sheet2.


I have highlighted the area of code which is not working to my intend
Array Arc has to be checked in Array NewArr. If the value doesn't match then that particular value has to be updated on the last row of Sheet2.


I hope I problem makes sence.... Cheers

Sub OTDarray()
    Dim x       As Long
    Dim y       As Long
    Dim wksTemp As Worksheet
    Dim var     As Variant
    Dim arr()   As Variant
    Dim Arc()   As Variant
    Dim Ar()   As Variant

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Add a new temporary sheet to build data up with
    Set wksTemp = Worksheets.Add
    
    'Loop over the worksheet objects
    For Each var In Array(Sheet3, Sheet4, Sheet5, Sheet6)
        With var
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            arr = .Cells(1, 1).Resize(x, 6).Value
            wksTemp.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
            Erase arr
        End With
    Next var
    
        Columns("B:E").Select
        Selection.Delete Shift:=xlToLeft
        
        
        
        With wksTemp
        
            Dim j As Long
            
            
                ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=Array( _
                "archive", "archived:quarter 1 & 2:q1", "archived:quarter 1 & 2:q2", _
                "archived:quarter 3 & 4:q3", "archived:quarter 3 & 4:q4", _
                "repair scheme archive (tds):q1", "repair scheme archive (tds):q2", "repair scheme archive (tds):q3", "repair scheme archive (tds):q4"), Operator _
                :=xlFilterValues
            
                Set Rng = ActiveSheet.AutoFilter.Range.Offset(0, 0).Resize(ActiveSheet.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(12)
                ReDim Ar(1 To ActiveSheet.AutoFilter.Range.Rows.Count - 1)
                
                    For Each rngArea In Rng.Areas
                        For Each rng1 In rngArea
                        i = i + 1
                        Ar(i) = rng1.Offset(0, 0)
                        Next rng1
                    Next rngArea
                
                ReDim NewArr(LBound(Ar) To UBound(Ar))
                    For k = LBound(Ar) To UBound(Ar)
                        If Ar(k) <> "" Then
                            j = j + 1
                            NewArr(j) = Ar(k)
                        End If
                    Next k
                ReDim Preserve NewArr(LBound(Ar) To j)
            
                Erase Ar
                wksTemp.Delete
                   
                End With
        
'++++++++++ Code doesn't work below this +++++++++
'++++++++++++++++++++++++++++++++++++++

        y = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
        Arc = Sheet2.Cells(1, 1).Resize(y).Value

For Each Item In NewArr
Dim u As String
u = Item

    If IsInArray(u, Arc) = True Then
        GoTo 45:
        MsgBox ("it works")
    End If
Sheet2.Cells(y + 1, 1).Value = u
y = y + 1
45:
Next Item
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    'Clear variables
    Set wksTemp = Nothing
    Erase NewArr
    
End Sub



'+++++++++ I don't know the correct function that has to be used for my requirement++++++++++
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Function IsInArray(stringToBeFound As String, Arc As Variant) As Boolean
  IsInArray = (UBound(filter(Arc, stringToBeFound)) > -1)
End Function

'Function IsInArray(stringToBeFound As String, Arc As Variant) As Boolean
    'IsInArray = IsError(Application.Match(stringToBeFound, Arc, 0))
'End Function

This is code that I used.

It would be great if someone to correct me in my logic or suggest better method