Compare two Ranges In Excel

Question

I am trying to compare data from two ranges. "A" contains 1,2,3,4 and "B" contains 1,2,5. I want to find those that are in "B" but not in "A", which is 5. Below is my code but I can't seem to get what I want. Can someone please advise?

Dim a As Range, b As Range, i As Integer, x As Integer
Set a = Range("A1:A4")
Set b = Range("B1:B3")

For i = 1 To b.Count
  For x = 1 To a.Count

    If b(i, 1) = a(x, 1) Then

    Else
      MsgBox (b(i, 1))
      Exit For
    End If
  Next x
Next i

Answer 1

This is a very small range but still I would recommend using Arrays to store your range values and then use the arrays for comparison. See this example.

Sub Sample()
    Dim Ar1, Ar2
    Dim i As Long, j As Long
    Dim Found As Boolean

    Ar1 = Range("A1:A4"): Ar2 = Range("B1:B3")

    For i = LBound(Ar2) To UBound(Ar2)
        Found = False
        For j = LBound(Ar1) To UBound(Ar1)
            If Ar1(j, 1) = Ar2(i, 1) Then
                Found = True
                Exit For
            End If
        Next j

        If Found = False Then Debug.Print Ar2(i, 1) & " Is unique"
    Next i
End Sub

EDIT

Another way (I still prefer the above way though)

Sub Sample()
    Dim rngA As Range, rngB As Range
    Dim aCell As Range, bCell As Range
    Dim Found As Boolean

    Set rngA = Range("A1:A4"): Set rngB = Range("B1:B3")

    For Each aCell In rngB
        Found = False
        For Each bCell In rngA
            If bCell.Value = aCell.Value Then
                Found = True
                Exit For
            End If
        Next

        If Found = False Then Debug.Print aCell.Value & " Is unique"
    Next
End Sub

Answer 2

or, if you'd like a non-VBA solution using Excel worksheet functions, try this formula in a column like Column C

=IF(ISERROR(FIND(B:B,A:A)),B:B&" is not found","")

Philip