Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined erro
Which line is causing the error?
-- HTH... Jim Thomlinson " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined error
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 -- HTH Bob Phillips wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined error
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined
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. -- HTH... Jim Thomlinson " wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run-time error '1004': Application-defined or object-deifined
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. -- HTH... Jim Thomlinson " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming | |||
"Run Time Error 1004 Application Defined or Object Defined Error." | Excel Programming | |||
Runtime Error 1004 -- Application Defined or Object Defined Error | Excel Programming |