VBA Macro Breaks Excel 2003

[TechTank]

Hi All,

First post so please be gentle I've written the following macro to insert a row, copy some cells from another sheet and then paste them into the new row. The problem occurs when I use this macro more than once it crashes Excel 2003 out! Any help greatly appreciated:

### Code Begins ###

Option Explicit
Option Compare Text
Sub InsertRow()
Dim rFind As Long
On Error Resume Next

'Disables Sheet Protection
ActiveSheet.Unprotect

'Stop Updating of The Screen To Optimise Speed
Application.ScreenUpdating = False

'Prevent Prompting
Application.DisplayAlerts = False

'If rFind > 0 Then Rows(rFind + 1).Insert xlShiftDown
'Selects Entire Last Row
Sheets("Format Control").Select
Rows("3:3").Select

'Copies Entire Last Row
Selection.Copy

'Selects the Environment Information Sheet
Sheets("Environment Information").Select

rFind = Columns("A:A").Find(What:="0", After:=Range("A5"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row

If rFind > 0 Then Rows(rFind + 1).Insert xlShiftDown

'Enable Prompting
Application.DisplayAlerts = False

'Update The Screen
Application.ScreenUpdating = True

'Enables Sheet Protection
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _

ActiveCell.Offset(4, -3).Select

End Sub

### Code Ends ###


My knowledge of VBA is minimal to be honest but if anyone has any suggestions I would be very grateful.

*Edit* I've just had an error appear from 'Microsoft Visual Basic' stating 'Automation Error'. This doesn't seem to affect any other macros in this workbook and there are a few.

Thank you all for your time.

 

[TechTank]

The 'Microsoft Visual Basic Automation Error' is a Error 404.

I combined this code from various sources I'd found on the internet but it seems the last line 'ActiveCell.Offset(4, -3).Select' is what's causing the macro to 404 on me.

I commented it out and the macro can run 15 times without error. Leave that line in and it crashes after 2 (3 times if I'm lucky)!

Thanks for reading and for any help.

 

[energman58]

Should you not set ApplicationDisplayAlerts to TRUE at the end of your code not FALSE?

 

[energman58]

In any case the problem is probably that your line:

'ActiveCell.Offset(4, -3).Select'

Moves the active cell 3 columns left each time you run the code. If you run it several times it will run until the point at which it is less than 3 columns in and that then causes the code to crash - if you set Display.Alerts to True you will get the normal debug message which is now being skipped and causes the 404 error as the code cannot proceed.

Are you sure that you want to offset from the Active Cell in this way? If you do you need to trap the error to make sure you dont try to select a non-existent cell.

 

[TechTank]

Hi Energman58,

I noticed my mistake with the TRUE just after I posted <newbie mistake> sorry.

Thank you for spotting my mistake with the offset. I shall have a sharp word with myself but thank you so much for your quick response and the heads up on the offset.

I was trying to get the active cell to be in the second cell of the new row but have yet to work out how to make this to happen.

Off topic > I see you are in Oman. I've visited Oman (Muscat and Thumrait primarily) several times and found your country to be very beutiful and the people so friendly and it shows with your post and helpfulness.

Thank you so much for the tips and help.

 

[Peter_SSs]

I was trying to get the active cell to be in the second cell of the new row but have yet to work out how to make this to happen.

Try replacing that problem line with

Cells(rFind + 1, 2).Select

(Don't forget to remove the "On Error Resume Next" line.)

 

[TechTank]

Hi Peter,

That works a charm!!! Thank you so much. I was starting to look at selecting unlocked cells and all sorts of complex work arounds :0)

 

[Peter_SSs]

Hi Peter,

That works a charm!!! Thank you so much. I was starting to look at selecting unlocked cells and all sorts of complex work arounds :0)

Great. It is good that you have something that works for you now.

Your code has a lot of 'selecting' in it. Selecting is rarely necessary and slows your code considerably. In your short code you probably wouldn't notice the difference, but I suggest that you keep it in mind for the future.

 

[energman58]

You are welcome

Oman is still nice - just starting to cool down now - 38 today and 26 tonight!

 

[TechTank]

Hi energman58 & Peter SSs,

I have posted a new question asking for help on inserting rows in a particular way (as ths post was an error fix) http://www.mrexcel.com/forum/showthread.php?t=576667. This might explain what I'm trying to accomplish a bit better and if you have any ideas of how I can make this work or even improve it.

Thank you both for your help, I was floundering considerably before you stepped in.

Regards,

Mark.