+ Reply to Thread
Results 1 to 4 of 4

Restart Loop or Sub After Error

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Minnesota
    MS-Off Ver
    Office 365
    Posts
    163

    Restart Loop or Sub After Error

    My code essentially starts with a loop, and I'm trying to make it restart either the loop or the sub, either one, after an error. Not sure how to do this, and Google isn't helping me.

    Code is below, but my goal of this part of the code was to find a document number and activate that cell (so I can use activecell in later parts of the code), but to have the code show a pop up stating no document number and then restart if the document number isn't found.

    Sub blah()
    
    Dim Answer As String
    Dim Looper As String
    
    Looper = "a"
    
    Do While Not Looper = vbNullString
        Answer = InputBox("Document Number?")
    
        If Answer = vbNullString Then
            Exit Sub
        Else
            On Error GoTo NoDocNumber
            Range("G1").Select
            Cells.Find(What:=Answer, LookIn:=xlValues).Activate
        End If
    
    NoDocNumber:
         MsgBox ("Document Number " & Answer & " was not found.")
    
         'NOW HOW TO MAKE IT RESTART THE SUB OR LOOP
    
    End Sub
    Also - is this an ideal way to find things (Control F style) via VBA, or is there a better way to do this?

    Thanks for your time and assistance!

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,164

    Re: Restart Loop or Sub After Error

    How about
    Sub OpieWinston()
    
    Dim Answer As String
    Dim Fnd As Range
    
       Do While Fnd Is Nothing
          Answer = InputBox("Document Number?")
          
          If Answer = vbNullString Then
             Exit Sub
          Else
             Range("G1").Select
             Set Fnd = Cells.Find(What:=Answer, lookIn:=xlValues)
             If Not Fnd Is Nothing Then
                Fnd.Activate
                Exit Sub
             End If
          End If
          
          MsgBox ("Document Number " & Answer & " was not found.")
       Loop
    End Sub

  3. #3
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Minnesota
    MS-Off Ver
    Office 365
    Posts
    163

    Re: Restart Loop or Sub After Error

    Excellent work around - thank you!

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,164

    Re: Restart Loop or Sub After Error

    You're welcome & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Count Duplicate in a column up to 5 and restart back at 1 (loop)
    By Norlina Deli in forum Excel Programming / VBA / Macros
    Replies: 38
    Last Post: 12-19-2018, 03:33 AM
  2. [SOLVED] Excel VBA - Loop through recordsets ADODB with For Loop Error - Help
    By ironfelix717 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-27-2018, 08:44 AM
  3. Replies: 1
    Last Post: 02-21-2018, 12:02 PM
  4. [SOLVED] Go to next loop iteration if current loop has error
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-03-2016, 09:54 PM
  5. [SOLVED] On Error Restart Application and continue function
    By MarBoTJ in forum Access Tables & Databases
    Replies: 2
    Last Post: 10-23-2014, 02:13 PM
  6. HOW TO: Pause loop, fix error on a popup UserForm, continue loop
    By AndyMachin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-19-2014, 04:37 PM
  7. Replies: 3
    Last Post: 06-14-2011, 04:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1