Bad code = CreateObject('scripting.dictionary") failure

[Big Lar]

I’m attempting to add a “Y” to ws1, Column Q
This only adds “Y” from the last item in ws6.

I do not understand what I’m doing wrong.

Sub MacroUpdateY2()
   Dim Cl As Range
   Dim sKey As String
   Dim ws1 As Worksheet
   Dim ws6 As Worksheet
   Set ws6 = Sheets("6")
   Set ws1 = Sheets("1")
 
   With CreateObject("scripting.dictionary")
      For Each Cl In ws6.Range("A2", ws6.Range("A" & Rows.Count).End(xlUp))
         sKey = Trim(Cl.Value)
      Next Cl
 
      For Each Cl In ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp))
         If sKey = (Cl.Value) Then
 
    Cl.Offset(, 14).Value = "Y"
        
End If
  Next Cl
   End With
 
End Sub

<strike></strike>

 

[Fluff]

Try

Sub BigLar()
   Dim Cl As Range
   Dim ws1 As Worksheet
   Dim ws6 As Worksheet
   Set ws6 = Sheets("6")
   Set ws1 = Sheets("1")
 
   With CreateObject("scripting.dictionary")
      For Each Cl In ws6.Range("A2", ws6.Range("A" & Rows.Count).End(xlUp))
         .Item(Trim(Cl.Value)) = Empty
      Next Cl
 
      For Each Cl In ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp))
         If .Exists(Trim(Cl.Value)) Then Cl.Offset(, 14).Value = "Y"
      Next Cl
   End With
End Sub

 

[Big Lar]

Brilliant!
Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback