Run-time error with copy-and-insert rows macro

Greg - Jan 13, 2016 at 12:46 PM
 Greg - Jan 15, 2016 at 02:47 PM
I am using the following macro to copy and insert rows in an Excel spreadsheet, based on a user inputted value. I place the cursor in the desired row, run the macro, which prompts for a number of rows. I enter a value and the macro works for awhile. However, after a few entries, I get an error message:

"Run-time error '-2147417848 (80010108)': Method 'Insert" of object 'Range' failed."

I am thinking that something is causing an "overload" which shuts down the macro. I would greatly appreciate help to resolve this problem. Thanks!


Sub Copy_And_Insert_Rows()
Dim x As Integer
x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
If x = False Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(x - 1, 0)).EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

1 response

Blocked Profile
Jan 13, 2016 at 05:31 PM
You have x as an integer, when clearly it is being tested as a boolean. Try testing it for 0 or 1.
Hi, ac3mark,
Thanks for your reply and suggestion... Would you please clarify what you mean by testing x for 0 or 1? Do you mean revising the fourth line of the macro to read:

If x = 0 then exit sub

Blocked Profile
Jan 14, 2016 at 04:41 PM
Yes. You cannot test an integer for true and false. You can only test it for a whole number; a number that is not a fraction, or decimal.

If you wish to test for true and false, set X as Boolean (DIM X AS BOOLEAN)
Thank you, ac3mark! Greatly appreciated! I made the change and the macro is working.
Blocked Profile
Jan 14, 2016 at 04:56 PM
You are welcome. Always come back if you are stuck and need help! Did you change the variable type, or the qualifying test?
I changed it from false to 0.

I also found that after entering a value (for number of rows to insert), that if I click OK in the dialogue box, all works well. If I use the enter key on my keyboard, an error message will sometimes appear. ?? No big deal, unless you see an immediate explanation. I will just be sure to use the OK button. Thanks!