Help with Find and Replace over multiple rows

Hello everyone,

I'm writing here because I fear that I what I want this program to do is not possible in VBA. From what I've heard, find and replace can only replace single values and can't work with multiple values, but hopefully I am mistaken on that.

I've attached an example of the data I'll be working with to make this easier to explain. Basically, the column with the data that needs to be modified is column H. Right now, the only values in that column are either 1's or 0's (in groups of 5 in a row). I need the values in those rows changed depending on where they are.

For all the 1's, I need the the group of 5 1's changed from 1,1,1,1,1 to 6,7,8,9,10.

For the 0's, I need them to be changed depending on where they are in relation to the 1's. If a group of 0's is directly before a group of 1's, then I need it changed from 0,0,0,0,0 to 1,2,3,4,5. If a group of 0's is directly after a group of 1's, then I need it changed from 0,0,0,0,0 to 11,12,13,14,15.

If there happens to be a case that looks like this 1,1,1,1,1,0,0,0,0,0,1,1,1,1,1, then I need those 0's changed to 11,12,13,14,15 (these 0's that come directly after the 1's always need to be changed to that, regardless of if another group of 1's follows those 0's).

If anyone could help me out with using find and replace over multiple rows I'd be very appreciative. I need to group all the data depending on the number in column H (1's go with the 1's, 2's go with the 2's, etc.) to find the averages of each one and I know that will be very easy to do after they are numbered like this because I can just sort them at that point.

I'd be very grateful for any help. Thank you.

Hello mundellj :
Could you provide a sample worksheet that display both before AND after samples?

Absolutely! I've attached the before and after. Sheet1 is the raw data, and Sheet2 is how I would like the data to look after it has been renumbered. Clearly I did this by hand, and the averages below each numbered set of data aren't really necessary for this program since that takes so little time to do anyway, but that's how we need the data to be output at the very end.

Is this not possible using find and replace? Does anyone know what would be the best way to go about this then? (What specific function could help)

I've just thought of a much easier way to explain this and would also probably be an easier way to do it as well.

I'd first want the code to find all instances of "1,1,1,1,1,0,0,0,0,0" (that specific sequence) and change it to "6,7,8,9,10,11,12,13,14,15".

Then I'd want it to find all instances of "0,0,0,0,0" (that specific sequence) and change it to "1,2,3,4,5".

Lastly, I'd want it to find all instances of "1,1,1,1,1" (that specific sequence) and change it to "6,7,8,9,10".

If it was done in that order, then it would always work since the five 0's that follow the five 1's always need to be changed to "11,12,13,14,15". After those were changed, all of the remaining 0's would all need to be changed to "1,2,3,4,5" and all the remaining 1's would need to be changed to "6,7,8,9,10" irregardless of where they are in relation to the other numbers.

Hopefully I explained that well enough. At this point, if someone could just at least point me in the right direction of a function that can find specific sequences in columns then I could probably figure the rest out of my own. If I can find the the right function to do this, then I'll probably just write 3 different programs, the first one finding all instances of "1,1,1,1,1,0,0,0,0,0", the second one finding all instances of "0,0,0,0,0", and the third one finding all instances of "1,1,1,1,1". If anyone can tell me of a function that could do that, I would greatly appreciate it. Thanks!

I've tried to do a modified version of the code seen in the thread linked to below, but I can't get it to work on my own data. I've only gotten it to work when I replicated his code and data exactly.

http://www.mrexcel.com/forum/showthread.php?t=290680

When I tried to add more Ptrns (Ptrn4, Ptrn5, etc) the code ended up not bolding what I had defined as a pattern. Is it possible to do what I want with this code? If I can just get this code to work, then I can probably just make it so it finds and replaces instead of just bolds the patterns.