+ Reply to Thread
Results 1 to 16 of 16

Getting Error 1004 Object or Application Defined Error - Code to rearrange data

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    This code is meant to rearrange a set of data I have based off the values in column A. The data is currently arranged in blocks of 5-8 rows that only have unique data in column B. I am trying to read through column A and move the data to a new sheet. Depending on the value in column A, the data from column B is placed under that header in the new sheet. All the data from one block is placed in the same row. I have attached the dummy data excel sheet that contains a similar data set. The first sheet shows how the data currently is formatted. The second sheet shows how I want the data to look.
    At the bottom is the code I have so far. I am new to VBA so from what I understand this code could me much shorter and it would run faster. I am not really worried about how fast it runs just so long as it will do what I want. I was finally able to get the debugger to show and highlight the line with the error, unfortunately it didn't help me.

    This is the line that is highlighted
    Please Login or Register  to view this content.
    I have tried multiple lines. Originally the code looked more like this:
    Please Login or Register  to view this content.
    But I received the same error. I have tried to SET the activeworkbook.worksheets("Sheet1") to be wCurrent and the other to be wNew but I was still getting the same errors. I posted another thread here. It started out really disorganized and I think it may be throwing people off. Only one person has tried to help but they really haven't said anything but that they will help me eventually and I think they have already forgot. I don't want to keep bumping old outdated threads.

    Thanks,

    Troll

    Please Login or Register  to view this content.
    NOTE: the special case is set up so that on certain data blocks one column A value may be adjacent to multiple column B values, it takes all of those cells and puts them in multiple rows under the right header. Then it adds rows to my data block counter so that the next row doesn't overwrite that data.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Need to change all lines
    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Since "Name" is exact match, I do not think you need to use a name like function. I would put it this way

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Thanks for the quick reply! I see I had the column and rows mixed up so I am changing what you suggested as well as all the other times I referenced a column,row to a row,column. I'll let you know when I have finished and we will see how it runs.

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Thanks again. I was able to fix all of that code but I got an error like the one seen here. So I am fixing all my copy and paste functions.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Blazzed,
    The link took me to a different site. Please post the corrected code(All lines) showing which line shows you an error.

  7. #7
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    I'm actually not getting the error anymore. But I don't think it is working logically. So I will have to check values and see where the logical errors are. Here is the 'working' (not errors) code.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    I'm also trying to +rep with the little star but it's not loading anything. I have some other work I need to finish up then I'll be back and I'll try and get that +rep for you.

    Thanks again.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,238

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    How is the code not working?

    Is the wrong data going to the wrong place, data being missed?
    If posting code please use code tags, see here.

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Blazzed,
    I have got the same issue. The site has been very slow and keep crashing.
    I have improved the code for the first part, but somehow lost on the second part. We can improve on the second once I get your feedback. The second part of the code is wrong, it is just I do not understand the lines.
    Attached Files Attached Files

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,238

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Try this, it appears to produce the expected results.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Sorry if you guys just got a random comment. When I came to look none of those solutions were here. The Code Norie posted is working as intended. I made one small change. I had to wait to add to the dataBlockCounter. It was just a visual error though. Thank you both very much. My data has finally been rearranged properly. +rep to both of you. I think the site was acting up before and that was why I couldn't give you rep AB33, but it has been given now.

  13. #13
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    D***, I must be a little slow today. I just now realized that file AB33 uploaded had a macro added to it. I like the use of case. It is working quite well. I think it may be just a little faster than the edits Norie made to my code. Once the database I am rearranging grows I can see speed becoming an issue so I have decided to use that macro. Thanks again. You guys are awesome!

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,238

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    I think the reason the other code is quicker is because it's not going through the same no of rows.

    AB33 added code to find the last row of data, I stuck with the hard-coded value in the original code.
    Last edited by Norie; 06-10-2013 at 12:08 PM.

  15. #15
    Registered User
    Join Date
    06-04-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    Yea, I noticed that little line. I had at one point used that to find the last row, it was stored as an integer FinalRow. I think it may still be floating around on the old thread. But when I started running into errors I figured a static value was fine. But as I said before, when the database grows, it will be nice to not have to edit that value manually. His code came up with an error because the dataBlockCounter was initialized. I just copied parts of his into your code. The best of both!

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,238

    Re: Getting Error 1004 Object or Application Defined Error - Code to rearrange data

    If you don't want to manually change the value then use the code AB33 used to find the last row.

    Oh, and instead of Integer declare as Long.

+ 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