# Find and replace a pattern of cells in a single column

1. ## Find and replace a pattern of cells in a single column

I started a new thread because the new way I want to solve this problem is much easier to understand and is simpler to explain. Plus this new title is much more descriptive of my problem. If anyone could delete or lock my old thread for me, that'd be great as I couldn't figure out how to do it through the edit button. Here's the link to my old thread:

http://www.excelforum.com/excel-prog...iple-rows.html

Basically, all I need is a program that can look through a single column of data, find a pattern of cells (a pattern that stretches across multiple rows), and then replace those cells in the column with data that I define.

I've attached an excel file with the data I'm working with. The single column I am talking about is all the data underneath the H column.

First, I need all instances of "1,1,1,1,1,0,0,0,0,0" changed to "6,7,8,9,10,11,12,13,14,15"

THEN

I need all instances of "1,1,1,1,1" changed to "6,7,8,9,10"

THEN

Lastly, I need all instances of "0,0,0,0,0" changed to "1,2,3,4,5"

If these are done in this specific order, then all my data will be numbered exactly how I need it. If anyone who knows of function that can do this, I would be GREATLY appreciative. Long hours of searching on Google has only given me very little results, and of those results it is either code that doesn't work or other threads like this with no one being able to solve this problem.

Thanks for looking at my thread guys. I'd be grateful for any help.

Cheers

P.S. If the order of replacing these patterns is giving anyone trouble, it would not be a problem at all if this took 3 seperate programs to accomplish.

2. ## Re: Help with finding and replacing a pattern of cells in a single column

Is there not a VBA function that can do this?

3. ## Re: Find and replace a pattern of cells in a single column

Maybe this can get you started towards a solution?

4. ## Re: Find and replace a pattern of cells in a single column

Thanks for the response WHER. That looks good, but how did you get the numbers into those new columns? Or did you just manually do it?

5. ## Re: Find and replace a pattern of cells in a single column

see attachment, run macro "test"

6. ## Re: Find and replace a pattern of cells in a single column

watersev, it works great! Thanks for your help, I've been trying to figure this out for days!

There are currently 1 users browsing this thread. (0 members and 1 guests)