Code to find, copy and add

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
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
Joined
Apr 23, 2005
Messages
467
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 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

Well-known Member
Joined
Jul 16, 2002
Messages
565
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
Joined
Feb 13, 2004
Messages
4,814
Hi

Slight change

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("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

Active Member
Joined
Apr 23, 2005
Messages
467

ADVERTISEMENT

Thanks ACW I miised that.
 

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

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
Joined
Jul 16, 2002
Messages
565
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 :cry:
 

will_simpson

New Member
Joined
Oct 17, 2006
Messages
29
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

Well-known Member
Joined
Jul 16, 2002
Messages
565
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......
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top