VBA Macro Breaks Excel 2003

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
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.
 
Last edited:

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
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

Well-known Member
Joined
Oct 25, 2010
Messages
553
Should you not set ApplicationDisplayAlerts to TRUE at the end of your code not FALSE?
 

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
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

Board Regular
Joined
Sep 5, 2011
Messages
92
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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
Code:
Cells(rFind + 1, 2).Select
(Don't forget to remove the "On Error Resume Next" line.)
 

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,244
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 25, 2010
Messages
553
You are welcome

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

TechTank

Board Regular
Joined
Sep 5, 2011
Messages
92
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.
 

Forum statistics

Threads
1,082,109
Messages
5,363,193
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top