VBA Excel 2010 Run-time Error 1004 - Application-defined or Object-defined Error

[Ross Tran]

Hi,

I am a new analyst working on a Macro-enabled worksheet. I ran into some rather popular run-time error while trying to run my macros (i.e., create, delete) multiple times on the same worksheet. I research on the internet and found that the error occurred because my object/variable has not been released every time I re-run the macro. The quick workaround is that I save my worksheet every time I make a change before I run the macros; however, there seems to be a better solution that would get rid of this problem entirely by changing the code to "re-define" my objects every I run the macros. Since I am pretty new to VBA, I am seeking some help with the below lines of code:

Sub Delete_Icons()

' INTERSECT COMMAND DETERMINES IF A SHAPE EXISTS WITHIN A SPECIFIED RANGE.
' IF THERE IS A SHAPE WITHIN ROWS 3 AND 1000 THEN IT WILL BE DELETED.
' THIS DOES NOT DELETE THE MACRO BUTTON IN ROWS 1 AND 2.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Dim shp As Shape
For Each shp In ActiveSheet.Shapes
' this below line caused run-time error 1004
If Not Intersect(shp.TopLeftCell, Range("A3:F1000")) Is Nothing Then
shp.Delete
End If
Next shp

</code>Any help to fix the run-time error in this line of code would be appreciated. Thanks.

Regards,

Ross

 

[Colin Legg]

Welcome to the forum, Ross.

From reading your code, my diagnosis of the problem is slightly different to yours. I think the issue is you haven't qualified that Range property and your code is sitting in a sheet class module. This means the code will error if the sheet containing the code isn't the active sheet.

To elaborate on that a bit more, it is possible that shp.TopLeftCell is returning a reference to a range on the active sheet but Range("A3:F1000") is returning a reference to a range on a different sheet: range objects in VBA are strictly 2 dimensional and cannot span across sheets which means you get a runtime error when it tries to intersect them. If I'm right then you can fix the problem by qualifying the Range property with Activesheet:

If Not Intersect(shp.TopLeftCell, [COLOR=#ff0000]ActiveSheet.[/COLOR]Range("A3:F1000")) Is Nothing Then

Or you could move your code into a standard code module. When unqualified Range properties sit in a standard code module they will refer to ranges on the active sheet.

 

[NeedleFelter]

Hi. I have read the Run-Time Error 1004 Application defined or object defined error explanations, but I am still stumped on the Format Deals subroutine. I receive the error at the line "Columns("G:G").Select". Any insight appreciated.

_____________________________________________________________________
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With

ActiveWindow.Zoom = 90

End If


sheetnum = sheetnum + 1


Loop



Sheets("sheet1").Cells.Delete


'Sub FormatDeals()


' Color green deals over X days and red over $XXXXX
Sheets("Midwest").Select
Columns("G:G").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With

 

[NeedleFelter]

Sorry - forgot to add that it is a run-time and compiler error in Excel 2007. Subroutine is to conditional format the Midwest deals on the Midwest tab in green over X number of days (redacted) and in red the deals on the Midwest tab over $XXXXX (redacted).

The code extends more and then ends in "End Sub".