Exit Sub On Error

Yevette

Active Member
Joined
Mar 8, 2003
Messages
336
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! :eek:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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