return a value into a vlookup cell

[nbqleebarnes]

Hi All,
I hope somebody can help, I have been busy for days trying to get this right with no joy.
I am trying to copy a cell value into a table with the relevant corresponding part number.
I have attached the workbook.
I have managed to use a vlookup to locate the relevant current stock part number and qty from the invoice but I am trying to update the stock level from "Sales" "J18" to the relevant part number "i18" stock level in the "Sales" worksheet.
It would be much appreciated if someone can help me.
Thanks in advance.
Lee

[nbqleebarnes]

Hi,
I don't think my explanation of what I am trying top do was very good.
So I will try to be more descriptive, I have also added a simpler example of what I have been battling with.


Sub paste()
Call srch
Sheets("Sales").Select
Range("j18").Select
Selection.Copy


End Sub




Sub srch()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim srchres As Variant


Set ws1 = Worksheets("Master Invoice")
Set ws2 = Worksheets("Sales")


On Error Resume Next
srchres = Application.WorksheetFunction.VLookup(ws1.Range("a15"), ws1.Range("A15:k38"), 11, False)
On Error GoTo 0
If (IsEmpty(srchres)) Then
ws2.Range("e1").Formula = CVErr(xlErrNA) ' Use whatever you want
Else
ws2.Range("e1").Value = srchres
Call srch2


End If
End Sub


Sub srch2()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim srchres As Variant


Set ws1 = Worksheets("Master Invoice")
Set ws2 = Worksheets("Sales")


On Error Resume Next
srchres = Application.WorksheetFunction.VLookup(ws1.Range("a15"), ws1.Range("A15:k38"), 1, False)
On Error GoTo 0
If (IsEmpty(srchres)) Then
ws2.Range("d1").Formula = CVErr(xlErrNA) ' Use whatever you want
Else
ws2.Range("d1").Value = srchres
End If
End Sub




So I have searched and found the first item on the invoice and written it into the "sales" worksheet in "d1" and "e1".
I am just trying to paste "e1" into column "c" using the value in "d1" as a lookup value.
I hope this helps a bit.
Sorry for being a total newbie, but I can't get this right.
Thanks
Lee