Looping / Resume Next??

kod2th3e

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

kod2th3e

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

kod2th3e

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Err will be zero if the Find method succeeds. If there is an error Err.Clear resets it.
 

Forum statistics

Threads
1,082,109
Messages
5,363,193
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top