Compare values from array1 and if result is found store it in 2nd array and not found copy to third array

[nishandsouza]

Dear all,

I am looking for the code to compare the each values in an array and an active excel sheet.

1. I have already an array
2. Compare the values in the array to a active excel sheet, if value is found store it in a New Array2
3. If no found then store it in another array 3.

Once this is done, i have two list boxes, where in i ll print the found and the not found numbers on these list boxes.

Thank you in advance.

 

[Fluff]

Something like

Sub test()
   Dim a1 As Variant, a2 As Variant, a3() As Variant, a4() As Variant
   Dim i As Long, j As Long, k As Long
   
   a1 = Application.Transpose(Sheets("Pcode").Range("A2:A100"))
   a2 = Application.Transpose(Sheets("Sheet1").Range("A2:A100"))
   For i = 1 To UBound(a1)
      If IsNumeric(Application.Match(a1(i), a2, 0)) Then
         j = j + 1
         ReDim Preserve a3(1 To j)
         a3(j) = a1(i)
      Else
         k = k + 1
         ReDim Preserve a4(1 To k)
         a4(k) = a1(i)
      End If
   Next i
End Sub