+ Reply to Thread
Results 1 to 4 of 4

Help with find, copy, and paste code using offset

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Help with find, copy, and paste code using offset

    Hey guys,

    I had recieved some help with this code before in this forum, and I thought it was working perfectly but it turns out that in a certain instance it messes up. Hopefully someone can help edit this code so that it works in all circumstances, as I've pored over it for a while and can't figure out how to fix it.

    Basically, this code looks for instances of groups of five 1s in a row in the H column and then copy and pastes those five 1s (plus the five 0s before it and the five 0s after it, if possible) into a new worksheet. If there aren't a total of five 0s before or after the five 1s, then it doesn't copy the 0s, only the 1s. Here are some examples to better explain what this program does.

    If the H column looks like this: 0,0,0,0,0,1,1,1,1,1,0,0,0,0,0
    Then it returns these rows: 0,0,0,0,0,1,1,1,1,1,0,0,0,0,0

    If the H column looks like this: 1,1,0,0,0,1,1,1,1,1,0,0,0,0,0
    Then it returns these rows: 1,1,1,1,1,0,0,0,0,0

    If the H column looks like this: 0,0,0,0,0,1,1,1,1,1,0,0,1,1,1
    Then it returns these rows: 0,0,0,0,0,1,1,1,1,1

    If the H column looks like this: 1,1,1,0,0,1,1,1,1,1,0,0,0,1,1
    Then it returns these rows: 1,1,1,1,1

    The problem exists when there are instances of this: 0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0
    If that instance occurs, the code will only return this: 0,0,0,0,0,1,1,1,1,1
    What I want it to return is this: 0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0

    If there are 2 groups of five 1s in a row, then the program now just skips the second group and any subsequent 0s after it. That is the problem I am trying to solve.

    Here is the code as it stands now:

    Please Login or Register  to view this content.
    I've also attached data that has instances of these 2 groups of five 1s in a row so as to better explain this situation. However, I also need this code to function if there are more than 2 groups of five 1s in a row (3 groups, 4 groups, etc.)

    Thanks for your help, I can't figure out for the life of me what to change in this code. Any help is appreciated

    P.S. Here's a link to my original thread: http://www.excelforum.com/excel-prog...tatements.html
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Help with find, copy, and paste code using offset

    hi, mundellj, check attachment, as to my view I would suggest a bit different and shorter way to decide your problem, run code "Start". As you did not specify how you'd like to have the result, all the selected rows are copied to a new sheet all together (all groups in one table)
    Attached Files Attached Files
    Last edited by watersev; 02-28-2011 at 05:41 AM.

  3. #3
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with find, copy, and paste code using offset

    Hey watersev,

    Thanks for your response. I pressed the start button you put on the worksheet and the program ran. However, I don't think it gave me back all of the data I needed. In the original data there were 20 instances of five 1s in a row, but your program only produced 13 of those 20. I only pressed the start button as I didn't really understand your instructions too well. Should I have done something else?

  4. #4
    Registered User
    Join Date
    01-28-2011
    Location
    Indy, IN
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Help with find, copy, and paste code using offset

    I think I didn't explain well enough what I wanted the program to do. My examples weren't clear enough, so I'll try to better explain now.

    If there are ANY 1s in that group of zeros before or after the 1s, then I don't want that data copied.

    So if the data reads 1,1,0,0,0,1,1,1,1,1,0,0,0,0,0 or 1,1,1,0,0,1,1,1,1,1,0,0,0,0,0 (or anything else where there aren't FIVE zeros before the 1s)
    Then I want the data that is copied to be 1,1,1,1,1,0,0,0,0,0

    The same goes for the zeros AFTER the 1s. I only want the five numbers before or after the 1s to be copied if they are ALL zeros.

    Sorry for the confusion and I hope this better explains my problem. The code I have now does that perfectly except in situations like this: 0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0

    The program returns me this: 0,0,0,0,0,1,1,1,1,1
    When I really want this: 0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0

    So in situations where there are multiple groups of five 1s directly after one another, the program screws up. Any suggestions?

+ 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