Looping / Resume Next??

kod2th3e

Board Regular
Joined
Apr 2, 2008
Messages
87
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:

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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:

Code:
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?
 
Upvote 0
You could do that, but why not deal with the error in line?

Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top