Announcement

Collapse
No announcement yet.

On Error reset

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • On Error reset



    I am having a problem resetting "On Error" after using it once.

    I enter a subroutine and encounter the "On Error goto PauseToInsert" code. As I'm searching for a string in a range of cells in Excel using the "Find" function, the string cannot be found, so the code execution transfers to "PauseToInsert". At that point, a line is inserted and the string is placed on the new line. I have a "Resume ExitNow" code and at ExitNow the only code is "Exit Sub".

    So, I exit the sub and eventually enter the sub again. This time, "Find" finds the new string OK. However, if it cannot find a second string, it does NOT go to "PauseToInsert".

    Here is my code:
    Code:
       Sub Sub1()
          
       '  blah blah blah
       
          TryAgain = 0
          Do Until TryAgain = 1
            Call Sub2
          Loop
          
       End Sub
          ----------------------------
       Sub Sub2()
          On Error GoTo PauseToInsert
          Cells.Find(What:="Name", After:=ActiveCell, LookIn:=xlValues, _
             LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
             xlNext).Activate
       
          '  blah blah blah
    
         TryAgain = 1
            Exit Sub
          
       PauseToInsert:
          TryAgain = 0
          Selection.Insert Shift:=xlDown
          ActiveSheet.Cells(InsrtRow, 1) = CustName
          On Error GoTo 0
          Resume ExitNow
          
       ExitNow:
          Exit Sub
          
       End Sub
    Any ideas how to get VB to go to "PauseToInsert" a second time so I can insert a second row???
    Last edited by royUK; July 29th, 2005, 16:48.

  • #2
    Re: On Error reset

    Please use Code tags when posting code, it looks cool, and make it much easier to read!

    You must get into the habit of returnng error handling back to Excel after using an On Error command. Basically there are two modes of Error handling in Excel. The first is the normal where Excel deals with Errors automatically by displaying an error message, the second is where a user has writen a macro to state wht he/she wants Excel to do when it comes across an Erro. This state is not reset automatically when a procedure finishes, so you must always hand error handling back to Excel after using an On Error command. To do this use:

    Code:
    On Error Goto 0
    after the line that is producing an error.

    In your example this line was missing from the very end.

    EG:
    Code:
    Sub Sub2()
    On Error GoTo PauseToInsert
    Cells.Find(What:="Name", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
    xlNext).Activate
    On Error goto 0
    ' blah blah blah
    
    TryAgain = 1
    Exit Sub
    
    PauseToInsert:
    TryAgain = 0
    Selection.Insert Shift:=xlDown
    ActiveSheet.Cells(InsrtRow, 1) = CustName
    On Error GoTo 0
    Resume ExitNow
    
    ExitNow:
    On Error Goto 0
    Exit Sub
    
    End Sub
    Hope this helps

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Comment


    • #3


      Re: On Error reset

      I have no idea what you mean by "Code tags".

      I made a test with your suggestions and everything worked well! Thank you.
      However, when I applied the same suggestions to the code I'm having trouble
      with, it does not work.

      Here is the test that worked:

      #
      Code:
      Sub macro2()
         
         Dim cnt1     As Integer
         Dim CusName  As String
         Dim TryAgain As Integer
         
         For cnt1 = 1 To 3
           TryAgain = 0
           CusName = ActiveSheet.Cells(cnt1 + 1, 5)
           Do Until TryAgain = 1
             Range("B7:B15").Select
             Call Macro3(CusName, cnt1, TryAgain)
           Loop
         Next cnt1
         
      End Sub
      
      Sub Macro3(CusName As String, rowid As Integer, TryAgain As Integer)
         
          On Error GoTo ABC
          Selection.Find(What:=CusName, After:=ActiveCell, LookIn:=xlValues, _
             LookAt:=xlWhole, searchOrder:=xlByColumns, _
             SearchDirection:=xlNext).Activate
          On Error GoTo 0
          TryAgain = 1
          Exit Sub
          
      ABC:
         TryAgain = 0
         Rows("9:9").Select
         Selection.Insert Shift:=xlDown
         ActiveSheet.Cells(9, 2) = CusName
         ActiveSheet.Cells(9, 3) = ActiveSheet.Cells(rowid + 1, 6)
         On Error GoTo 0
         Resume ExitNow
         
      ExitNow:
         On Error GoTo 0
         Exit Sub
         
      End Sub
      #

      Of course, you do not have the Excel spreadsheet to see the data that is being
      pulled in.

      Could I send you the other file via regular e-mail with an attachment so you can
      see what I mean?
      Please do not email me with questions that could be posted in the Forum.

      For more information regarding Code Tags read the Rules.

      Alastair
      Einstein:
      Things should be made as simple as possible, but not any simpler

      Be sure to check out TemplateZone for all your Microsoft Office Needs.
      Get OfficeReady Professional 3.0 here!

      Comment

      Working...
      X