Copy If Not Exists

[billandrew]

Good Afternoon all

Looking to copy values [to another worksheet] from Column H which are not in Column T

This Script highlights column H values not in Column T - hoping for some insight how to attain the above request.

Thanks guys.

Sub CompareRangesII()    
    Dim r As Range
    Dim Dict As Object
    Dim Sht1 As Worksheet
    
    Set Dict = CreateObject("scripting.dictionary")
    Set Sht1 = Worksheets("Test")
    
    With Dict
    .CompareMode = vbTextCompare
    For Each r In Sht1.Range("T2", Sht1.Range("T" & Rows.Count).End(xlUp))
    If Not .exists(r.Value) Then .Add r.Value, r.Row
    Next r
    'Look through list in Column H and asertain if exists in Column T
    
    For Each r In Sht1.Range("H1", Sht1.Range("H" & Rows.Count).End(xlUp))
    If Not .exists(r.Value) Then
    r.Interior.Color = RGB(184, 205, 208)
    'this highlights Column H values Not in Column T list
    r.Font.Bold = True
        End If
        Next r
        End With
    Set r = Nothing


    End Sub

 

[billandrew]

Application defined or object defined error

Thank You fluff.....

 

[Fluff]

Ok, try

Sub CompareRangesII()
   Dim r As Range, Rng As Range
   Dim Dict As Object
   Dim Sht1 As Worksheet
   
   Set Dict = CreateObject("scripting.dictionary")
   Set Sht1 = Worksheets("Test")
   
   With Dict
      .CompareMode = vbTextCompare
      For Each r In Sht1.Range("T2", Sht1.Range("T" & Rows.Count).End(xlUp))
         If Not .Exists(r.Value) Then .Add r.Value, r.Row
      Next r
      'Look through list in Column H and asertain if exists in Column T
      
      For Each r In Sht1.Range("H1", Sht1.Range("H" & Rows.Count).End(xlUp))
         If Not .Exists(r.Value) Then
            If Rng Is Nothing Then Set Rng = r.Offset(, -7).Resize(, 12) Else Set Rng = Union(Rng, r.Offset(, -7).Resize(, 12))
            r.Interior.Color = RGB(184, 205, 208)
            'this highlights Column H values Not in Column T list
            r.Font.Bold = True
         End If
      Next r
   End With
   Set r = Nothing
   If Not Rng Is Nothing Then Rng.Copy Sheets("Sheet4").Range("A1")
End Sub

 

[billandrew]

Remarkable

Thank you sir

 

[Fluff]

You're welcome