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

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?

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?