Looping / Resume Next??

[kod2th3e]

I am trying to create code to control an inventory tracking sheet. I have 4 sheets in my workbook: Master, Add, Remove, & Not Found. On the Master sheet I have a list of products each with a unique ordering part #. On my sheet named Add I have a list that gets generated by ordering part number and quantity that I want to have update the sheet named Master and if there are any errors I want it to copy the errors on the sheet named Not Found. I have this working for the most part except when I run the code any of the errors that it finds it will sum up the quantities and place that number on the sheet named Add. If someone could help me figure out how to make it stop doing that part, it'd be greatly appreciated. Below is the code that I have:

Sub updateadd()
Application.ScreenUpdating = False
On Error GoTo copydata
    Dim strbopn As String
    Dim strquantity As Integer
    Dim rnum As Integer
        rnum = 2
    Dim lr As Long
    Dim lc As Long
        lc = Range("A" & Rows.Count).End(xlUp).Row + 1
 
    Do Until rnum = lc
        strbopn = UCase(Cells(rnum, 1))
        strquantity = Cells(rnum, 2)
        Sheets("Master").Select
            Cells.Find(What:=strbopn, After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
            ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value + strquantity
        rnum = rnum + 1
        Sheets("Add").Select
        If rnum = lc Then
            Exit Sub
        End If
    Loop
copydata:
    Sheets("Add").Select
    Range((Cells(rnum, 1)), (Cells(rnum, 2))).Copy
    Sheets("Not Found").Select
        lr = Range("A" & Rows.Count).End(xlUp).Row + 1
    Cells(lr, 1).Select
    ActiveCell.PasteSpecial
    Err.Clear
    Sheets("Add").Select
    Resume Next
End Sub

 

[Andrew Poulsom]

Where do you want to resume if there is an error?

 

[kod2th3e]

Where do you want to resume if there is an error?

I think if it can skip over the section of code where it states:

ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value + strquantity

and start again at the rnum = rnum +1

would this be accomplished by using a goto statement and putting a reference in front/above that line?

 

[Andrew Poulsom]

You could do that, but why not deal with the error in line?

Sub updateadd()
    Application.ScreenUpdating = False
    On Error Resume Next
    Dim strbopn As String
    Dim strquantity As Integer
    Dim rnum As Integer
        rnum = 2
    Dim lr As Long
    Dim lc As Long
        lc = Range("A" & Rows.Count).End(xlUp).Row + 1
    Do Until rnum = lc
        strbopn = UCase(Cells(rnum, 1))
        strquantity = Cells(rnum, 2)
        Sheets("Master").Select
            Cells.Find(What:=strbopn, After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
            If Err = 0 Then
                ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(0, 2).Value + strquantity
            Else
                Err.Clear
                Sheets("Add").Select
                Range((Cells(rnum, 1)), (Cells(rnum, 2))).Copy
                Sheets("Not Found").Select
                lr = Range("A" & Rows.Count).End(xlUp).Row + 1
                Cells(lr, 1).Select
                ActiveCell.PasteSpecial
                Sheets("Add").Select
            End If
        rnum = rnum + 1
        Sheets("Add").Select
        If rnum = lc Then
            Exit Sub
        End If
    Loop
End Sub

 

[kod2th3e]

Thanks Andrew,
That works perfect. Question will the err always equal 0 or does it equal that because it gets cleared from the previous loop?

 

[Andrew Poulsom]

Err will be zero if the Find method succeeds. If there is an error Err.Clear resets it.