Find Range of Cells using a Criteria

**newbie1234** · 12-27-2008, 11:05 AM

I have this scenario..Im not use if this is possible in excel...

I have a Range of cells in 1 column..E.g. Below

A
B
B
B
A
A
B
B
A

It continues on to a few 100k. Now I have a criteria which i will use to search for my range of cells.

Criteria e.g.
A
A
B
B

So i want to search for this particular pattern in my Long list of cells and locate the exact location of the pattern i specify. How can i Do this in Excel...

Anyone care to shed some light on this.

**MickG** · 12-27-2008, 11:45 AM

Hi, This code will search in column "A" for the Pattern you show in your thread:-
Nb:- If you alter the Pattern, and the new pattern is the same size, you can just alter the Array "Ray". If larger or smaller you will need to alter the Number of "Offsets" in the variable "oVal" and the "resize" number in the address.

Please Login or Register  to view this content.

Regards Mick

**newbie1234** · 12-27-2008, 11:54 AM

Originally Posted by MickG

Hi, This code will search in column "A" for the Pattern you show in your thread:-
Nb:- If you alter the Pattern, and the new pattern is the same size, you can just alter the Array "Ray". If larger or smaller you will need to alter the Number of "Offsets" in the variable "oVal" and the "resize" number in the address.

Please Login or Register  to view this content.

Regards Mick

Hi there,

Thanks for the code..I tried running it under my button event and it works.. Im kinda confused about the altering.. Let say My criteria now is ABBAABB how to i alter the code accordingly? Also the use of Msgbox isnt good sorry for that, since when I have alot of matches it just expands all the way to the bottom and i got no way to scroll..

**Steve R** · 12-27-2008, 12:12 PM

Maybe you can use the "String Concatenation" vba function here.

Then you can use FIND("AABB",A1) to get the start row number of the string criteria "AABB".

**MickG** · 12-27-2008, 01:00 PM

Hi, Try:-
Data in Column "A".
Insert Pattern in "B1".
Results in Column "C"

Please Login or Register  to view this content.

Regards Mick

**shg** · 12-27-2008, 03:56 PM

You could use this function:

Please Login or Register  to view this content.

For example,

Please Login or Register  to view this content.

**newbie1234** · 12-28-2008, 12:43 AM

Thank You so much everyone for you kind help..

Find Range of Cells using a Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Find Range of Cells using a Criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions