run-time error '1004': Application-defined or object-deifined error

[rich5665]

I am getting this error with the following Macro any help would be
appreciated.


Sub Process_Service_Fees()

Dim RangeDepth As String
Dim RangeDepth2 As String

RangeDepth = Worksheets("Sheet1").Range("F3").Value

For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth)
' Iterate through each element.

If MyObject.Value = "" Then '
If Cell is empty
Rows(MyObject.Row & ":" & MyObject.Row).Select
'Delete the row
Selection.Delete Shift:=xlUp
'Shift next line up one
End If
Next

RangeDepth2 = CInt((Worksheets("Sheet1").Range("F2").Value) / 1.5)

For Each MyObject In Worksheets("Sheet1").Range("A1:A" & RangeDepth2)
' Iterate through each element.

If MyObject.Value = "" Then '
If Cell is empty
Rows(MyObject.Row & ":" & MyObject.Row).Select
'Delete the row
Selection.Delete Shift:=xlUp
'Shift next line up one
End If
Next

Columns("F:F").Select 'Delete empty columns.
Selection.Delete Shift:=xlToLeft ' These three columns are
always empty
'Columns("J:J").Select ' and do not change, unless
file layout
' Selection.Delete Shift:=xlToLeft ' changes.
'Columns("L:L").Select
' Selection.Delete Shift:=xlToLeft

End Sub

 

[Guest]

Which line is causing the error?

 

[Bob Phillips]

You seem to introduce cell from nowhere. The For Next loop uses MyObject,
then you test cell which has not been assigned.

Use Option Explicit, you will never regret it

 

[rich5665]

Jim,

The debugger indicates this line as the problem:

For Each MyObject In Worksheets("Sheet1").Range("A1­:A" & RangeDepth)
' Iterate through each element.

A co-worker was using this Macro without issue and it looks like
something changed.



Bob,

Happily I didn't create the Macro, I've been asked to fix it though.

 

[Guest]

What is the value of RangeDepth when the program fails (It should be the same
as the value in F3 on sheet 1). If that value is not an integer between 1
and 32,700 then this will crash as it can not define the range to be
traversed.

 

[Guest]

I forgot to add as Bob refered to add the line "Option Explicit" at the top
of the code module (prior to any procedures) and the select Debug -> Compile.
As a guess it will take you to a variable that was not declared. Declare the
Variable (Dim ... as ...) and then repeat Compile Debug until the program
successfully compiles.