vba - Excel VBA检查值是否不在范围内

Question

我有两个应该相同的范围(尽管它们的排序可能不同)。我试图在 rangeA 中找到不在 rangeB 中的任何值。

我能够找到显示值是否在某个范围内匹配的示例，但如果不匹配则很难找到任何东西。

到目前为止，我有:

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

但是当它们匹配时，它会将所有值显示为不匹配。

Answer 1

尝试这个

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