How would I change this code to work in a slightly different way. I have a value in Sheet1("A4") and I want to lookup this value on Sheet2("A1:F20") with the value being in column F. I then want to put the corresponding value from Column A in Sheet1("A6"), the next corresponding value in Sheet1("A16"), the next on Sheet1("A26"). I would also like to count how many times the value show up in Column F and put that number in Sheet1("B4"). Hope that makes sense. thanks!
Sub helpexcel()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Cl As Range
Set Ws1 = ThisWorkbook.Sheets("Sheet3")
Set Ws2 =Workbooks("Data").Sheets("Sheet1")
Application.ScreenUpdating = False
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each Cl In Ws2.Range("A5", Ws2.Range("A" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Cl.Offset(, 22).Value
Next Cl
For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
Cl.Offset(, 12).Value = IIf(LCase(.Item(Cl.Value)) = "empty", "Sold out", .Item(Cl.Value))
Next Cl
End With
End Sub
Sub helpexcel()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Cl As Range
Set Ws1 = ThisWorkbook.Sheets("Sheet3")
Set Ws2 =Workbooks("Data").Sheets("Sheet1")
Application.ScreenUpdating = False
With CreateObject("scripting.dictionary")
.CompareMode = 1
For Each Cl In Ws2.Range("A5", Ws2.Range("A" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Cl.Offset(, 22).Value
Next Cl
For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
Cl.Offset(, 12).Value = IIf(LCase(.Item(Cl.Value)) = "empty", "Sold out", .Item(Cl.Value))
Next Cl
End With
End Sub
