Help with Find and Replace over multiple rows

[mundellj]

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.