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

 

[Fluff]

If you want to match col A on both sheets try

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 = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[helpexcel]

Thanks Fluff, works great as usual!

What if i wanted to alter this code to put another value in Ws1 depending on the value in Ws2? For example if the value is "Empty" on Ws2, the value would show as "Sold Out" on Ws1?

 

[Fluff]

Assuming that you are talking about column 23, would the cell be empty, contain a formula returning "", or would it have 0?

 

[helpexcel]

it would contain text so ""?

 

[Fluff]

If it contains text then it cannot be "" (ie nothing)
or do you mean that the word empty is in the cell?

 

[helpexcel]

Yes, the word "Empty" would be in the cell. So every cell that says empty would be transferred over as "Sold Out", everything else would be transferred as "In Stock"

 

[Fluff]

OK, try

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

 

[helpexcel]

Didn't work. Is it easier to put in separate code that looks at that column after the transfer and makes the changes?

 

[Fluff]

Please define "Didn't work"