+ Reply to Thread
Results 1 to 9 of 9

VBA Asymmetrical to symmetrical again

  1. #1
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145

    VBA Asymmetrical to symmetrical again

    I posted a similar file to the one I'm sending a while back. I thought the issue was resolved, but when I download the information, it comes out asymmetrical, and the code used to find it picks up some cells that are out of range.
    The situation is:
    I work with an accounting system called SAP, and ever so often I've got the "privilege" of updating a vendors list. The System spits out the information in a jumbled manner. I thought the information was uniformed, but to my dismay, it isn't. In Columns C, J, and M, on the sheet named "Confused Dump" I need to get the information relating to the cells beside the cells B8, B19, B22, B28, etc (vendor, City, Street, Pay Term, etc, I've highlighted them in green), and I need to put them on the sheet named "Completed_Format" in the format on that sheet.
    The columns always have the same data, but not in the same row sequence.
    I tried adapting a code seen here, but it didn't work. Is there some code that can solve my problem?
    I've attached the file with the information changed. The Confused_Dump information goes over 40000 lines on the original dump.
    Leith Ross gave me the following code, but it worked because most of the information was symmetrical.


    Please Login or Register  to view this content.
    Thanks,
    Xrull

  2. #2
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145

    The file

    I forgot to attach the file. Sorry
    Attached Files Attached Files

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Xrull,

    I will look over the workbook and see what innovative strategy I can come up with.

    Sincerely,
    Leith Ross

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Xrull,

    Can I rely on the "Vendor" being the start of the block and the end has an empty row?

    Sincerely,
    Leith Ross

  5. #5
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145
    Leith,
    Yes, you are correct, and there will always be an empty row between the last entry and the next vendor.
    Thanks,
    Xrull
    Last edited by Xrull; 01-13-2009 at 08:13 AM.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Xrull,

    I changed to macro to search for data names in the columns "B", "L", and "M". When a match is made, the contents of the cell to the right on posted to the "Completed" sheet under the proper heading. I added a button on the "Completed" sheet to run the macro. Here is the revised macro...

    Due to a Database error (Again!) on the forum server, I am unable to attach the workbook. You can replace the old macro with this one by copying this code and pasting it over the old macro. If the server comes back up, I'll post the workbook.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  7. #7
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145

    Few Adjustments

    Thanks Leith,

    I was able to adjust the code a little. I needed to pick up some items in column F, but the items are 2 column over, but I used these macros to fix up the sheet, is there an neater way to do this?
    Please Login or Register  to view this content.
    And if this method isn't efficient, and let me know what are the dangers of adjusting your code. I want to learn.
    Thanks,
    John
    Last edited by Xrull; 01-13-2009 at 11:43 PM.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hell Xrull,

    You did an excellent job on adding the extra column into the search. The macro will only take the contents of the cell that is to the right of the search cell. If I understand you correctly, column "F" needs to take extract the cell 2 columns to the right. This would require a further modification just for column "F". Perhaps something like this...
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  9. #9
    Forum Contributor
    Join Date
    07-29-2008
    Location
    New York
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    145
    Leith,
    The formula worked like a charm. I'm learning a lot from this site.
    Thanks,
    Xrull

+ 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