如何检查一个范围内的单元格是否存在于另一个范围内

Question

由于太久，我一直希望能够提供一些见解。

我试图循环一个单元格范围（varSKU1），检查每个单元格是否存在于另一个单元格区域（varSKU2）。如果该值不存在，我想将它作为新的单元格添加到varSKU2中。下面

代码：

Dim varSKU1(), varSKU2() As Variant 
Dim n, m As Integer 
Dim sku1, sku2 As Variant 
Dim rowCount1, rowCount2 As Integer 
Dim mFlag As Boolean 

rowCount1 = Sheets("SKUs").Cells(Rows.Count, "A").End(xlUp).row 
rowCount2 = Sheets("Cats").Cells(Rows.Count, "A").End(xlUp).row 

varSKU1 = Sheets("SKUs").Range("A2:A" & rowCount1).Value 
varSKU2 = Sheets("Cats").Range("A2:A" & rowCount2).Value 

m = rowCount2 + 1 

For Each sku1 In varSKU1 

    mFlag = False 

    For Each sku2 In varSKU2 

     If sku1 = sku2 Then 

      mFlag = False 
      Exit For 

     Else 

      mFlag = True 

     End If 

    Next sku2 

    If mFlag = True Then 

     Sheets("Cats").Range("A" & m).Value = sku1 
     Sheets("Cats").Range("B" & m).Value = "Misc" 
     Sheets("Cats").Range("C" & m).Value = "Miscellaneous" 

     m = m + 1 

    End If 

Next sku1 

眼下，该代码将所有值varSKU1为新的细胞来表（“猫”），无论在varSKU2是否存在价值。

Answer 1

试试这个：

主要问题是布尔值。开始每个循环为假，然后当它发现它相等改变它为真退出for，如果最后它没有找到匹配它仍然是假的然后测试，并做你需要的。

Sub code() 
Dim varSKU1 As Range, varSKU2 As Range 
Dim n&, m& 
Dim sku1 As Range, sku2 As Range 
Dim rowCount1&, rowCount2& 
Dim mFlag As Boolean 

rowCount1 = Sheets("SKUs").Cells(Sheets("SKUs").Rows.Count, "A").End(xlUp).Row 
rowCount2 = Sheets("Cats").Cells(Sheets("Cats").Rows.Count, "A").End(xlUp).Row 

Set varSKU1 = Sheets("SKUs").Range("A2:A" & rowCount1) 
Set varSKU2 = Sheets("Cats").Range("A2:A" & rowCount2) 
m = rowCount2 + 1 

For Each sku1 In varSKU1 
    mFlag = False 
    For Each sku2 In varSKU2 
     If cstr(sku1) = cstr(sku2) Then 
      mFlag = True 
      Exit For 
     End If 
    Next sku2 

    If mFlag = False Then 
     Sheets("Cats").Range("A" & m).Value = sku1 
     Sheets("Cats").Range("B" & m).Value = "Misc" 
     Sheets("Cats").Range("C" & m).Value = "Miscellaneous" 
     m = m + 1 
    End If 

Next sku1 
End Sub 

Answer 2

谢谢@ScottCraner，你肯定帮我缩小了我的问题。我保持布尔变化，但真正的决定因素是sku1和sku2的比较。无论出于何种原因，比较两个范围/变体正在挑剔。将它们转换为字符串后，我能够正确引用它们的文本值。我之前假设，我们在内存中引用索引或地址。

Sub FindMisc() 

Dim varSKU1 As Variant 
Dim varSKU2 As Variant 
Dim n, m 
Dim sku1, sku2 As Variant 
Dim rowCount1, rowCount2 
Dim mFlag As Boolean 


rowCount1 = Sheets("SKUs").Cells(Sheets("SKUs").Rows.Count, "A").End(xlUp).Row 
rowCount2 = Sheets("Cats").Cells(Sheets("Cats").Rows.Count, "A").End(xlUp).Row 

varSKU1 = Sheets("SKUs").Range("A2:A" & rowCount1) 
varSKU2 = Sheets("Cats").Range("A2:A" & rowCount2) 

m = rowCount2 + 1 

    For Each sku1 In varSKU1 

     mFlag = False 

     For Each sku2 In varSKU2 

      If CStr(sku1) = CStr(sku2) Then 

       mFlag = True 
       Exit For 

      End If 

     Next sku2 

     If mFlag = False Then 

      Sheets("Cats").Range("A" & m).Value = sku1 
      Sheets("Cats").Range("B" & m).Value = "Misc" 
      Sheets("Cats").Range("C" & m).Value = "Miscellaneous" 

      m = m + 1 

     End If 

    Next sku1 
End Sub