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.

 

[CharlesH]

Hi,

This may help.
Copy and paste to a module.
I used the VBA help for the find.

Sub FindItemandMove()
Application.ScreenUpdating = False
Dim lrow As Long
Dim firstaddress As Variant
Dim c As Variant
lrow = Sheets("Sheet2").Range("A65336").End(xlUp).Row + 1
With Sheets("Sheet1").Range("E:E")
    Set c = .Find("Item not found", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Row
        Do
            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]

Charles, thanks for your help. Created a new Module and pasted your code in. Ran the macro, but it doesn't seem to do anything, but it doesn't give me any errors either.

It's late here in the UK now, so I'll play with it tomorrow and get back to you. Thanks again

 

[acw]

Hi

Slight change

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

It also assumes that the formula in column E that produces the Item not found result is dynamic enough to cover any new entries made to sheet 2.


HTH

Tony

 

[CharlesH]

Thanks ACW I miised that.

 

[atmospheric]

Thanks for the help guys, but ACW, the code you offered appears to get stuck in a loop, effectively locking my system up (Thank God for C.A.D.)

I'm not sure I understand your assumption about the formula in Column E, so here it is:

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

(My initial explanation quoted Sheet1 and Sheet2. Sheet2 is actually titled Reference Sheet, but I altered this in your code, similarly, the "not found" msg)

The amended code:

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("Not Found, please add", LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Row
Do
lrow = Sheets("Reference Sheet").Range("A65336").End(xlUp).Row + 1
Range(Cells(c.Row, 1), Cells(c.Row, 2)).Copy Destination:=Sheets("Reference Sheet").Cells(lrow, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing 'And c.Row <> firstaddress
End If
End With
End Sub

I followed Charles instructions by pasting into a new module. Is this correct?

Could I ask you guys if you would kindly take another look at this code.

Many thanks again

 

[acw]

Hi

My comment about the formula in column E refers to the range it covers. Basically, looking at your formula, you have covered the rows 13:1000. However, if your new data goes beyond row 1000, then the vlookup formula will not pick up any new entries, and will never give the right result. This will result in a loop.

You need to make sure the formula range in this formula is dynamic enough to pick up any new entries.


Tony

 

[atmospheric]

I currently have 900 entries in the Reference sheet, room for 100 more before I need to amend the formula (not a problem).

But even so, the VLOOKUP is referring to the reference sheet and bringing detail to sheet1. I'm trying to get the code to work in the opposite direction, to bring new detail from sheet1 to the reference sheet. The VLOOKUP shouldn't have any effect on the code.

Having said all that, I've altered the formula to accomodate 2000 rows, just to be safe.

So I still have the problem with the code. interestingly enough, I don't get any error messages.

Please help

 

[will_simpson]

Try

Sub move_non_matches()

Sheets("Sheet1").Activate

On Error GoTo finisher

Do

Columns("F:F").Select

Selection.Find(What:="Item not found", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Range(ActiveCell(1, -4), ActiveCell(1, -3)).Copy

Sheets("Sheet2").Activate

Range("A1").Select

If ActiveCell <> "" Then

Selection.End(xlDown).Select

ActiveCell(2, 1).Select

End If

ActiveSheet.Paste

Sheets("Sheet1").Activate

Loop

finisher:

End Sub

Sorry not got Excel at home so not tested fully but think that'll do it.

Also in the vlookup use the lookup range A:E rather than using row references, assuming that the rest of the sheet below is clear and the cells above the data won't match on the lookup, then you never have to bother with the length of the range.

 

[atmospheric]

Thanks for that Will, this is driving me crazy!

The code finds the first instance of "not found" then does nothing else. Column E is highlighted and the first instance is selected. Nothing appears on Sheet2. (I corrected F:F to read E:E and my two earlier mentioned differences)

Convinced I was doing something wrong I inserted a simple MsgBox bit of code between finisher and the End sub command and that appears on screen correctly, so the code is running to the end. Still no error messages.

Help, before I lose any more hair......