Finding Identical Values in Two Columns

[vba-amateur]

Hello,


This time, I have to write a sub that checks if a value is duplicate in two columns. If it is, a message box should appear saying "blah." If it is not, a message box should appear saying "okay."


I have written a code. However, when I run it, I always get the message box saying "okay." This should not happen because there are definitely identical values in the two columns. Any help fixing my code would be great.

Sub Compare()
Dim r1 As Range, r2 As Range, rng1 As Range, rng2 As Range
Set r1 = Worksheets("Sheet1").Range("A2:A11")
Set r2 = Worksheets("Sheet1").Range("B2:B11")
    For Each rng1 In r1
          For Each rng2 In r2
               If rng1 = rng2 Then
                    MsgBox ("blah")
               Else
                    MsgBox ("okay")
               End If
          Next
     Next
End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[mjr veverka]

It's not true
This is how your macro works:

...
blah => A2 = B2
blah => A2 = B3
blah => A2 = B4
okay => A2 <> B5 Not the same values
blah => A2 = B6
blah => A2 = B7
blah => A2 = B8
blah => A2 = B9
blah => A2 = B10
blah => A2 = B11
blah => A3 = B2
blah => A3 = B3
blah => A3 = B4
okay => A3 <> B5 Not the same values
blah => A3 = B6
blah => A3 = B7
...
'and so on

[6StringJazzer]

You are comparing every cell in the A range to every cell in the B range and then giving a message for every comparison. You are showing 100 messages. What your code is doing is not what you described that you want it to do.

If you want to check each value in the A range, and give blah if it has a dupe in B and okay if not, then do this. This will give you just 10 messages, one for every value in the A range. I also expanded your message a bit:

Sub Compare()
Dim r1 As Range, r2 As Range, rng1 As Range, rng2 As Range
Dim Found As Range
Set r1 = Worksheets("Sheet1").Range("A2:A11")
Set r2 = Worksheets("Sheet1").Range("B2:B11")
    For Each rng1 In r1
          Set Found = r2.Find(what:=rng2, LookAt:=xlWhole, LookIn:=xlValues)
          If Found Is Nothing Then
             MsgBox rng1.Address & " value " & rng1.Value & " is not found in range " & r2.Address
          Else
             MsgBox rng1.Address & " value " & rng1.Value & " is found in " & Found.Address
          End If
     Next
End Sub

[Trebor76]

Hi there,

Here's one way:

Option Explicit
Sub Compare()
    
    Dim r1 As Range, r2 As Range
    Dim rngMyRange As Range
    Dim rngMyCell As Range
    Dim varMyResult As Variant
    
    Set r1 = Worksheets("Sheet1").Range("A2:A11")
    Set r2 = Worksheets("Sheet1").Range("B2:B11")
    
    Set rngMyRange = Worksheets("Sheet1").Range("A2:B11")
    
    For Each rngMyCell In rngMyRange
        If rngMyCell.Column = 1 Then
            varMyResult = Evaluate("VLookup(" & rngMyCell.Address & "," & r2.Address & ", 1, False)")
            If IsError(varMyResult) = False Then
                MsgBox rngMyCell.Value & " is in the range " & r2.Address
            Else
                MsgBox rngMyCell.Value & " is Not in the range " & r2.Address
            End If
        Else
            varMyResult = Evaluate("VLookup(" & rngMyCell.Address & "," & r1.Address & ", 1, False)")
            If IsError(varMyResult) = False Then
                MsgBox rngMyCell.Value & " is in the range " & r1.Address
            Else
                MsgBox rngMyCell.Value & " is Not in the range " & r1.Address
            End If
        End If
    Next rngMyCell
    
End Sub

Please use code tags when posting code as I have done. Thank you.

Robert