CODEWITHSUNDEEP
excelvba
user388197
user388197

Reputation: 119

Compare two Ranges In Excel

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

Upvotes: 4

Views: 6718

Answers (3)

Joseph
Joseph

Reputation: 5160

I tend to prefer native VBA functions like Find(). I find them a bit faster to use, especially when you're working with a large range.

Here's another solution:

Option Explicit
Sub test()
    Dim r1 As Excel.Range
    Dim r2 As Excel.Range

    Set r1 = Sheet1.Range("A1:A4")
    Set r2 = Sheet1.Range("B1:B3")

    Dim s() As String

    s = getUniques(r1, r2)

    Dim i As Long
    For i = 0 To UBound(s)
        Debug.Print s(i)
    Next i
End Sub


Function getUniques(ByRef r1 As Excel.Range, ByRef r2 As Excel.Range) As String()
    Dim cell As Excel.Range
    Dim found As Excel.Range

    Dim uniques() As String
    Dim i As Long

    For Each cell In r2
        On Error Resume Next
        Set found = r1.Find(cell.Value)
        On Error GoTo 0

        If (found Is Nothing) Then
            ReDim Preserve uniques(i)
            uniques(i) = cell.Value
            i = i + 1
        End If
    Next cell

    getUniques = uniques
End Function

Upvotes: 2

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

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","")

enter image description here

Philip

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149297

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

enter image description here

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

Upvotes: 3

Related Questions