# Code to find, copy and add

#### atmospheric

##### Well-known Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### CharlesH

##### Active Member
Hi,

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

Code:
``````Sub FindItemandMove()
Application.ScreenUpdating = False
Dim lrow As Long
Dim c As Variant
lrow = Sheets("Sheet2").Range("A65336").End(xlUp).Row + 1
With Sheets("Sheet1").Range("E:E")
If Not c Is Nothing Then
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

##### Well-known Member
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

##### MrExcel MVP
Hi

Slight change

Code:
``````Sub FindItemandMove()
Application.ScreenUpdating = False
Dim lrow As Long
Dim c As Variant
With Sheets("Sheet1").Range("E:E")
If Not c Is Nothing Then
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

##### Active Member

Thanks ACW I miised that.

#### atmospheric

##### Well-known Member
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:

(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 c As Variant
With Sheets("Sheet1").Range("E:E")
If Not c Is Nothing Then
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

##### MrExcel MVP

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

##### Well-known Member
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.

#### will_simpson

##### New Member
Try

Sub move_non_matches()

Sheets("Sheet1").Activate

On Error GoTo finisher

Do

Columns("F:F").Select

:=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

##### Well-known Member
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......

Replies
13
Views
498
Replies
7
Views
470
Replies
11
Views
411
Replies
3
Views
97
Replies
9
Views
266

### Forum statistics

1,136,613
Messages
5,676,820
Members
419,653
Latest member
analyticalchemist94

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back