Transferring data from 1 sheet to Another

[helpexcel]

Having Trouble with this code.

I'm looking up Column A on Sheet3 on Sheet1. When it finds a match on sheet1 its supposed to take column 23 and put the results in column 13 on Sheet3. Right now its just matching Column A with Column 5 on Sheet3 and putting that value in column 13.

Sub vlookup()


Dim lastrow, lastrow2, i As Long
Dim Searchfor, j, inarr As Variant


Set ws1 = ThisWorkbook.Sheets("Sheet3")
Set ws2 = Workbooks("Data").Sheets("Sheet1")




With ws2
    lastrow = .Cells(Rows.Count, "E").End(xlUp).Row
    inarr = Range(.Cells(5, 5), .Cells(lastrow, 23))
End With




With ws1
    lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row


    searcharr = Range(.Cells(2, 1), .Cells(lastrow2, 1))


    outarr = Range(.Cells(2, 1), .Cells(lastrow2, 13))
End With


On Error Resume Next
For i = 1 To lastrow2
For j = 5 To lastrow
Searchfor = searcharr(i, 1)


If inarr(j, 1) = Searchfor Then
    For kk = 1 To 13
        outarr(i, kk - 1) = inarr(j, kk)
    Next kk
    Exit For
End If


Next j
Next i


With ws1
    Range(.Cells(10, 13), .Cells(lastrow2, 13)) = outarr
End With


End Sub

 

[helpexcel]

Sorry. It pulled over all the values as is instead of changing "Empty" to "Sold Out" and the other values to "In Stock"

 

[Fluff]

It didn't change anything to "In Stock" because you never said it needed to!
If it didn't change Empty" to "sold out" then I suspect that none of the cells in col 23 contain the word "Empty" on it's own.
What EXACTLY is in those cells?

 

[helpexcel]

It's text, really not sure what it's not working. I've tried to re-enter the text and change the format, but it's not changing.

I tried to change the code for another purpose where I wanted to put the values in the column before. This is what i changed, which is totally wrong. Cl.Offset(, -1).Value = .Item(Cl.Value)

 

[Fluff]

Is the word Empty in col W on sheet1 of the Data workbook?

 

[helpexcel]

I changed the layout of sheet1 so the lookup value is in column E and "Empty" is in column U.
I updated the Code to:
For Each Cl In Ws2.Range("E2", Ws2.Range("E" & Rows.Count).End(xlUp)) .Item(Cl.Value) = Cl.Offset(, 16).Value

 

[Fluff]

Those changes are correct & the code works for me.
Make sure that you don't have any leading/trailing spaces along with empty in col U

 

[helpexcel]

Fluff - I figured it out. I had to change LCase to UCase. Now it works. THANKS!!

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[helpexcel]

@Fluff - 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!

 

[Fluff]

As this is a completely different question, you will need to start a new thread.