+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    rich5665@gmail.com
    Guest

    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. #2
    Jim Thomlinson
    Guest

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

    Which line is causing the error?
    --
    HTH...

    Jim Thomlinson


    "rich5665@gmail.com" 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. #3
    Bob Phillips
    Guest

    Re: 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

    <rich5665@gmail.com> wrote in message
    news:1123690855.712795.96400@g14g2000cwa.googlegroups.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. #4
    rich5665@gmail.com
    Guest

    Re: 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=AD: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. #5
    Jim Thomlinson
    Guest

    Re: 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


    "rich5665@gmail.com" 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. #6
    Jim Thomlinson
    Guest

    Re: 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


    "rich5665@gmail.com" 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1