Progressing through data range until conditions are satisfied.

[Binen]

Hi all,

I have a a long data sequence consisting of either 0s, 1s or 2s. By starting at a random point in the data I'm looking to calculate how many cells forward I need to progress until I come across 'X' number of cells in succession with values ≤ 'Y'. Hopefully that is clear.

As a quick example..
I decide my starting point is the 10th entry in my series (Column O). I require 3 cells (our X value) in succession to be less than or equal to 1 (our Y value).
In the example data I will have to step forward 7 entries to Column V until I reach the starting point that allows my conditions to be met. From this point there are three entries all less than or equal to 1. My desired Output value then for these parameters is 7.

Any way that this can be achieved? I'm guessing a 'Do Until' loop could work but am honestly not sure how or if there is a better way.
All thoughts are appreciated.

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
2	Starting Cell	10		Cell #	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25
3	Limit: <=	1		Data	1	2	2	1	1	1	2	2	1	2	2	1	1	2	1	2	1	1	0	1	2	2	2	1	2
4	Cells in a row	3													1	2	3	4	5	6	7
5	DELAY (output)	7

<tbody>
</tbody>

Sheet3​

 

[MickG]

With your actual data starting row2 and the results in "C5". Try this:-

[COLOR="Navy"]Sub[/COLOR] MG25Oct35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Lim [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
St = Range("c2").Value: Lim = Range("C3").Value: Num = Range("C4").Value
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(3, St + 5), Cells(3, Columns.Count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 0
    [COLOR="Navy"]For[/COLOR] n = 0 To Num - 1
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, n) <= Lim [COLOR="Navy"]Then[/COLOR] c = c + 1
    [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]If[/COLOR] c = Num [COLOR="Navy"]Then[/COLOR]
        Range("C5") = Dn.Column - (5 + St)
        [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Binen]

Mate that is spot on. I've plugged that into my actual data set and have been trying to break it all morning. It's been up to the task!
Can't thank you enough!

 

[MickG]

You're welcome