Vlookup Alternative

[helpexcel]

Hi - I'm using the code below to lookup data in Ws2 and place it in Ws1. Currently if the value in Ws1 Column A isn't found in Ws2 Column E it will put a "N" in Column P. How do I tweak it so that it only changes the cells in Column P when there is a match between Ws1 Column P and Ws2 Column E?

thanks!

 With CreateObject("scripting.dictionary")      
.CompareMode = 1
      For Each Cl In Ws2.Range("E45", Ws2.Range("E" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 16).Value
      Next Cl
      For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 15).Value = IIf(UCase(.Item(Cl.Value)) = "SOLD OUT", "Y", "N")
      Next Cl
   End With

 

[Fluff]

How about

      For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then Cl.Offset(, 15).Value = IIf(UCase(.Item(Cl.Value)) = "SOLD OUT", "Y", "N")
      Next Cl

 

[helpexcel]

getting a run time error '424' Object-required

 

[Fluff]

How have you changed your original code?

 

[helpexcel]

I just added the If .Exists piece.

 

[Fluff]

Can you please post you're modified code?

 

[helpexcel]

Just figured it out...I defined Cl as C1, they look alike in the coding.

 

[Fluff]

Glad you sorted it & thanks for the feedback