Lookup Data in Sheet1 on Sheet2 or Sheet3 and Return Value

[helpexcel]

Hi - How do I lookup an item on 2 lists with out the ws3 overriding the ws2 inputs? basically changing it so that if it cant find the item from ws1 in ws2, then look in ws3. This is the code I tried to modify to add ws3. thanks!

[COLOR=#333333]With CreateObject("scripting.dictionary") [/COLOR]
[COLOR=#333333].CompareMode = 1[/COLOR]
[COLOR=#333333]For Each Cl In ws2.Range("E45", ws2.Range("E" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333].Item(Cl.Value) = Cl.Offset(, 2).Value[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]Cl.Offset(, 1).Value = .Item(Cl.Value)[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333].Item(Cl.Value) = Cl.Offset(, 2).Value[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#333333]Cl.Offset(, 1).Value = .Item(Cl.Value)[/COLOR]
[COLOR=#333333]Next Cl[/COLOR]
[COLOR=#333333]End With[/COLOR]

 

[helpexcel]

yes, that code works perfectly.

 

[Fluff]

Ok, can you please explain, in words, what you are trying to do for the next part. Also when referring to columns please use the column letters, not numbers.

 

[helpexcel]

I figured it out. If I use the Create Object before each block of code it works great.

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(, 2).Value
        Next Cl
        For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
        If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 2).Value
        Next Cl
        For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 1).Value = .Item(Cl.Value)
         Next Cl
    End With
 
    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(, 15).Value
        Next Cl
        For Each Cl In ws3.Range("E45", ws3.Range("E" & Rows.Count).End(xlUp))
        If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 15).Value
        Next Cl
        For Each Cl In ws1.Range("A11", ws1.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 12).Value = .Item(Cl.Value)
        Next Cl
    End With

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[helpexcel]

thanks for your time and help, its appreciated!