Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to fix : vba runtime error 1004 application defined or object defined error

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to fix : vba runtime error 1004 application defined or object defined error

    Hi,

    I have been working on a set of data in excel for the past few days now. I am using a custom filter to get the data i want and then paste that data into a new worksheet. It was working fine untill today and now i cannot even select a row of cells without the error turning up.

    Here is the code i am trying to use;

    Code:
     
    Sub RevisedResults()
     
     
        Sheets("Test_Formatting").Select
     
        Selection.AutoFilter Field:=4, Criteria1:="<>*car*", Operator:=xlAnd _
            , Criteria2:="<>*plane*"
     
     
        Selection.AutoFilter Field:=5, Criteria1:="no"
     
     
     
     
        Range("A1").Select
        Columns("A").ColumnWidth = 20
     
        Range("B1").Select
        Columns("B").ColumnWidth = 37
     
        Range("C1").Select
        Columns("C").ColumnWidth = 20
     
        Range("D1").Select
        Columns("D").ColumnWidth = 125
     
        Range("E1").Select
        Columns("E").ColumnWidth = 13
     
        Range("F1").Select
        Columns("F").ColumnWidth = 13
     
        Range("G1").Select
        Columns("G").ColumnWidth = 13
        Range("H1").Select
        Columns("H").ColumnWidth = 19
     
        Range("I1").Select
        Columns("I").ColumnWidth = 15
     
        Rows("2:2").Select
        Selection.Insert Shift:=xlDown
     
        Range("A2:I2").Select
        Range("A2:I2").Value = "'======"
     
        Range("A1:I1").Select
        Selection.AutoFilter
    I can execute the search no problem but once i try to exceute the code in bold above i.e. "Range("A1").Select" i get the "1004" error.

    I have tried closing and opening excel but this hasnt worked.

    I have been copying and pasting this data a few times so that might be part of the problem. The data i am trying to use is roughly 150 rows in size.

    Any help would be greatly appreciated.


    Regards,
    Crunchie





    End Sub

  2. #2
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to fix : vba runtime error 1004 application defined or object defined error

    Well, you could just take out the Select statements entirely. You don't have to select a cell in the column to change the column width. Actually, you should be able to rewrite this entire macro to not have to select anything.

    Code:
    Sub RevisedResults()
     
     
        Sheets("Test_Formatting").Select
     
        Selection.AutoFilter Field:=4, Criteria1:="<>*car*", Operator:=xlAnd _
            , Criteria2:="<>*plane*"
     
     
        Selection.AutoFilter Field:=5, Criteria1:="no"
     
     
        Columns("A").ColumnWidth = 20
        Columns("B").ColumnWidth = 37
        Columns("C").ColumnWidth = 20
        Columns("D").ColumnWidth = 125
        Columns("E").ColumnWidth = 13
        Columns("F").ColumnWidth = 13
        Columns("G").ColumnWidth = 13
        Columns("H").ColumnWidth = 19
        Columns("I").ColumnWidth = 15
     
        Rows("2:2").Insert Shift:=xlDown
     
        Range("A2:I2").Value = "'======"
     
        Range("A1:I1").AutoFilter
    Kristy

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to fix : vba runtime error 1004 application defined or object defined error

    Hi Pookie,

    Thanks for looking at my post. I have removed all the "Range" calls from the code but left the "Rows("2:2").Insert Shift:=xlDown" command. Once i try to execute this command the 1004 error pops up again. Would there be any reason for this?

    Also, i opened a new excel program and copied the code+data to a new sheet and everything worked fine. Could it be a problem with the original workseet?

    If ive made anything unclear let me know.

    Thanks in advance

  4. #4
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to fix : vba runtime error 1004 application defined or object defined error

    It's possible it could be something with the sheet. For example, if you add a lot of rows, the program could eventually consider the end of your "used" range to be the last row on the sheet--even if it is blank--so when you try to add a row it has nowhere to go.

    Sometimes it just happens and starting with a fresh workbook just makes things easier
    Kristy

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to fix : vba runtime error 1004 application defined or object defined error

    Hi Pookie,

    Opening a new workbook seems to have done the trick so hopefully i wont have any more problems. Thank you very much for the help.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com