Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Exit Sub On Error

  1. #1
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exit Sub On Error

    Hello All,

    Can one of you vba experts tell me what the code is that will automatically stop my macro if a run-time error occurs? Or perhaps you could tell me how to "better write" the following macro so I don't get an error at all. I have a list of cells with numbers and what the macro does is insert "x" number of rows based on whatever the number is in the row (above) the active cell. For example:

    A1 = 8
    A2 = 2
    A3 = 5
    A4 = 3

    below A4 3 rows are inserted, below A3 5 rows are inserted, etc.

    The macro works from the bottom up but errors out when it gets to the top (A1):

    Sub FormatForm()

    Range("A1").End(xlDown).Offset(1, 0).Activate

    Do Until ActiveCell = "A1"
    ActiveCell.Offset(-1, 0).Activate
    Dim i As Integer
    For i = 1 To ActiveCell.Offset(-1, 0).Value
    ActiveCell.EntireRow.Insert
    Next
    Loop

    End Sub

    I'm sure it has something to do with my Do Until or maybe I need an Error On statement, but not quite sure. Help would be appreciated! Thanks a lot! :o

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Location
    Maidstone, Kent UK
    Posts
    1,470
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is not a good answer but it is getting late here

    Sub FormatForm()
    On Error GoTo GetOut
    Range("A1").End(xlDown).Offset(1, 0).Activate

    Do Until ActiveCell = "A1"
    ActiveCell.Offset(-1, 0).Activate
    Dim i As Integer
    For i = 1 To ActiveCell.Offset(-1, 0).Value
    ActiveCell.EntireRow.Insert
    Next
    Loop
    GetOut:
    End Sub

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    One way, inserting rows above:

    Sub Test1()
    'Insert up
    On Error GoTo EH
    Dim x As Long, y As Long
    Application.ScreenUpdating = False
    For x = Range("A65536").End(xlUp).Row To 1 Step -1
    y = Cells(x, 1).Value
    Cells(x, 1).Resize(y, 1).EntireRow.Insert
    Next x
    Application.ScreenUpdating = True
    Exit Sub
    EH:
    MsgBox "Cannot insert rows off the sheet." & vbCrLf & "Reduce the number of row to be inserted."
    End Sub


    One way, inserting rows below:

    Sub Test2()
    'Insert down
    Application.ScreenUpdating = False
    Dim InsertRange As Range, x As Long
    With Range(("A1"), Range("A65536").End(xlUp))
    Set InsertRange = .Cells(Rows.Count, 1).End(xlUp)
    x = 1
    Do Until x > InsertRange.Row
    If IsNumeric(.Cells(x)) And .Cells(x).Value > 0 Then
    x = x + 1
    Rows(x & ":" & x + .Cells(x - 1).Value - 1).Insert
    x = x + .Cells(x - 1).Value - 1
    End If
    x = x + 1
    Loop
    End With
    Application.ScreenUpdating = True
    End Sub


    Both ways avoid activating or selecting, and with common sense numbers you could avoid the error trap lines also.

  4. #4
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    Hi Tom,

    I am can use Subtest2, but I do not understand how it works, and therfore, should anything ever go wrong with the code I would not be able to debug it. Can you please explain what the code is doing line by line. Thanks.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    Line by line explanation:



    'Line 1
    'Subroutine macro name.
    Sub Test2()

    'Line 2
    'Turn off screen updating to speed up code and use less memory.
    Application.ScreenUpdating = False

    'Line 3
    'Declare variables - -
    '"InsertRange" is the list of numbers in column A such as you posted.
    '"x" is a Long variable passed to the row number.
    Dim InsertRange As Range, x As Long

    'Line 4
    'With structure for a dynamic range due to insertion of rows.
    'With structures make for more efficient code as the range
    'does not have to be repeated numerous times.
    With Range(("A1"), Range("A65536").End(xlUp))

    'Line 5
    'Set statement to define the original range in column A.
    'Note, "Cells(Rows.Count, 1)" is a reliable and more efficient
    'way to say "Range("A65536").End(xlUp)", because Excel versions
    'prior to Excel 5 only had 16384 rows.
    'The Cells() reference syntax is Cells(RowNumber, ColumnNumber).
    Set InsertRange = .Cells(Rows.Count, 1).End(xlUp)

    'Line 6
    'Look at rows starting with Row 1 (of Column A).
    x = 1

    'Line 7
    'Define the loop to instruct Excel to stop looking at cells
    'past the row where the original range's last value will be.
    Do Until x > InsertRange.Row

    'Line 8
    'If structure to determine if the value in the cell
    'being evaluated is a number, and is not zero.
    'Note, "IsNumeric" is used instead of the IsNumber function
    'in case the number in the cell is text-formatted.
    If IsNumeric(.Cells(x)) And .Cells(x).Value > 0 Then

    'Line 9
    'Define the row from which you will insert rows, which is
    'one row below the one you just looked at in Line 8.
    x = x + 1

    'Line 10
    'Define the Rows range after Line 9, and insert
    'the quantity of rows per the number in the cell
    'of the row above.
    Rows(x & ":" & x + .Cells(x - 1).Value - 1).Insert

    'Line 11
    'Define the next row you whose cell you will look at,
    'which is different than it was at the moment before Line 10
    'was executed. It depends on how many rows were inserted.
    'Notice the duplicate text "x + .Cells(x - 1).Value - 1"
    'that shows up in Line 10 and in Line 11 here.
    x = x + .Cells(x - 1).Value - 1

    'Line 12
    'Terminate the If structure.
    End If

    'Line 13
    'Refer to the next row.
    x = x + 1

    'Line 14
    'Terminate the loop structure.
    Loop

    'Line 15
    'Terminate the With structure.
    End With

    'Line 16
    'Restore screen updating.
    Application.ScreenUpdating = True

    'Line 17
    'End sub line to terminate the macro.
    End Sub

  6. #6
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    THANKS TOM!

    HAVE A GREAT EVENING/WEEKEND! CHAT WITH YOU SOON!

  7. #7
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    Yevette - -

    Following up on your question a couple posts ago, keep in mind that if the code errors, it might be a circumstance where the data is organized differently on the sheet than your example presented it. Examples: if the numbers are in column B instead of column A; or if a number like 65535 was entered, and rows cannot be inserted "off the sheet". So if you get a run time error, the response might not be to debug any code, but rather to have the sheet's data fit the code, or the code fit the sheet's data.

  8. #8
    Board Regular Yevette's Avatar
    Join Date
    Mar 2003
    Location
    Los Angeles, CA
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exit Sub On Error

    Hi Tom,

    I understand. Thanks so much for the instruction/explanations. It really helps me learn! and again, the code you provided is working like a charm! many many thanks.
    F.O.C.U.S.: Follow One Course Until Successful

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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