Excel VBA check if value is not in range

Question

I have two ranges that should be identical (albeit they may be sorted differently). I'm trying to find any values in rangeA that are not in rangeB.

I'm able to find examples that show if values ARE matched in a range, but struggling to find anything if they aren't.

So far I have:

Sub Compare2()

    Dim test1, cell As Range
    Dim FoundRange As Range

    Set test1 = Sheets("Names").Range("A1:A5")
    For Each cell In test1

        Set FoundRange = Sheets("Queue & Status").Range("A1:A200").Find(what:=test1, LookIn:=xlFormulas, lookat:=xlWhole)

        If FoundRange Is Nothing Then
            MsgBox (cell & " not found")    
        End If

    Next cell
End Sub

But it's showing all values as being not matched, when they are.

try changing test1 to cell in the Set FoundRange line. Set FoundRange = Sheets("Queue & Status").Range("A1:A200").Find(what:=cell, LookIn:=xlFormulas, lookat:=xlWhole) Tested and seems to work ok. — Captain Grumpy
– Captain Grumpy, Commented Dec 2, 2016 at 2:47
FYI Dim test1, cell As Range is declaring cell as a Range, and test1 as an implicit Variant. — Mathieu Guindon
– Mathieu Guindon, Commented Dec 2, 2016 at 2:51
Are you really wanting to LookIn formulas, or are you trying to compare values? (It won't matter which you do if all the cells are constants, but could cause problems if any of the data is calculated.) — YowE3K
– YowE3K, Commented Dec 2, 2016 at 3:17

nightcrawler23 · Accepted Answer · 2016-12-02 03:03:31Z

3

Try this

Sub Compare2()

    Dim test1 As Range
    Dim lookIn As Range
    Dim c As Range
    Dim FoundRange As Range

    Set test1 = Sheets("Names").Range("A1:A5")
    Set lookIn = Sheets("Queue & Status").Range("A1:A200")
    For Each c In test1

        Set FoundRange = lookIn.Find(what:=c.Value, lookIn:=xlFormulas, lookat:=xlWhole)

        If FoundRange Is Nothing Then
            MsgBox (c.Value & " not found")
        End If

    Next c
End Sub

answered Dec 2, 2016 at 3:03

nightcrawler23

2,0761 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nightcrawler23 Over a year ago

Yeah, i noticed that too, but it worked so i posted anyways

S M · Accepted Answer · 2016-12-02 02:49:52Z

0

what:=test1 should be what:=cell (or even what:=cell.value)

answered Dec 2, 2016 at 2:49

S M

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

Excel VBA check if value is not in range

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related