Code to find, copy and add

[atmospheric]

I have Sheet1 and Sheet2. Sheet2 is 1000 rows of products, details and prices. Into Sheet1 I paste a list of this weeks sales (by product), but the details supplied are not complete.

Using VLOOKUP, I compare each product on Sheet1 with the list on Sheet2 and bring across the additional information. If the item is not found on Sheet2, (because it's a new product), the VLOOKUP formula returns "Item not found".

Can someone help with the code to do the following:

If "Item not found" appears in column E of any Row of Sheet1, copy that Row,Columns A and B only, to Columns A and B to the next available Row on Sheet2.

This way, we'll know on Sheet2 which products are new and need to be updated, so hopefully they will be recognised next time.

Hope this makes sense, fire away with any questions if it doesn't, and if it does, thanks for your help in advance.

 

[will_simpson]

edited: double post due to crappy connection

 

[will_simpson]

The on error method of exiting the sub may be a bit clumsy and causing the issue.

You did amend the activecell lines too? Cause if not then the
Range(ActiveCell(1, -4), ActiveCell(1, -3)).Copy would also cause an error as Activecell(1, -4) is off the sheet from column E, and force it to end the Sub before it had pasted anything into Sheet2.

Try stepping through the Macro with F8 to see when the error trap activates, i suspect you'll find it is when it tried to execute that line above.

If so then Range(ActiveCell(1, -3), ActiveCell(1, -2)).Copy should fix that.

 

[atmospheric]

OK Will, that obviously contributed to the problem. I wasn't sure what the Active Cell line was all about, so I left it alone.

We seem to be getting somewhere now though. But, because I've dragged the VLOOKUP formula down 2000 cells, the code is getting stuck because a lot of the cells in column A are still empty, so the VLOOKUP is returning the "not found" msg.

I now need to amend the VLOOKUP to include if(A13="","",...............)
into the following:

=IF(ISNA(VLOOKUP($A13,'Reference Sheet'!$A$13:$E$1999,2,FALSE)),"Not Found, please add",(VLOOKUP($A13,'Reference Sheet'!$A$2:$C$1999,3,FALSE)))

I've tried all ways without success, if I can crack this, I should be able to put this one to bed.

Many thanks

 

[will_simpson]

=IF(A1="","",IF(ISERROR(VLOOKUP(A1,Lookup_range,2,0))=true,"NOT FOUND PLEASE ADD",VLOOKUP(A1,Lookup_range,2,0)))

There you, go put it to bed i hope.


And BTW, activecell works as below, if you imagine the cell you're in is the bold one, then the one above is (0,1), the one to the right is (1,2), just extend the matrix to refer to any other cell on the sheet.

0,0--0,1--0,2
1,0--1,1--1,2
2,0--2,1--2,2

 

[CharlesH]

Repost Vba

atmospheric,

The code that I and Acw posted you said it got stuck in a loop. I looked at the code and found part of the code reMarked. The follow code should work.
I tested this version and worked for me.

The "And c.Row" was remarked.

Sub FindItemandMove()
Application.ScreenUpdating = False
Dim lrow As Long
Dim firstaddress As Variant
Dim c As Variant
With Sheets("Sheet1").Range("E:E")
    Set c = .Find("Item not found", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Row
        Do
            lrow = Sheets("Sheet2").Range("A65336").End(xlUp).Row + 1
            Range(Cells(c.Row, 1), Cells(c.Row, 2)).Copy Destination:=Sheets("Sheet2").Cells(lrow, 1)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Row <> firstaddress
    End If
End With
End Sub

 

[atmospheric]

That's it guys, all sorted and working perfectly. Many thanks for your help and explanations, especially ACW.

That's 2.5 hrs saved every Monday morning. I'll be able to put my feet up!