Need formula or VBA count the delays

[motilulla]

Hello,</SPAN></SPAN>

Note: Colours in column D are filled just to explain the example purpose </SPAN></SPAN>

In the column D I got some sequence of numbers and I got a formula, which counts the length of 0's in the G6:G19 now I need a formula, which can count delay of the 0's length in H6:H19</SPAN></SPAN>

For example delay of the 0 with length 1, last 0 find in row 59 and the last data row is 65 so from the row "59 to 65" 0 is getting delay 6 </SPAN></SPAN>

For example delay of the 0 with length 2, last 00 find in row 21&22 and the last data row is 65 so from the row "22 to 65" 00 is getting delay 43 and so on for all delays...</SPAN></SPAN>

Here is an example... </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5				Of 1's		Length Of 0	Count Of 0	Delay
6				0		1	8	6
7				1		2	2	43
8				2		3	2	1
9				3		4	0
10				4		5	0
11				0		6	1	32
12				1		7	0
13				2		8	0
14				0		9	0
15				0		10	1	10
16				0		11	0
17				1		12	0
18				0		13	0
19				0		14	0
20				1
21				0
22				0
23				1
24				0
25				1
26				0
27				1
28				0
29				0
30				0
31				0
32				0
33				0
34				1
35				0
36				1
37				0
38				1
39				2
40				3
41				0
42				1
43				2
44				3
45				4
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55				0
56				1
57				2
58				3
59				0
60				1
61				2
62				0
63				0
64				0
65				1
Sheet4

Cell Formulas
Range	Formula
G6	{=SUM(0+(FREQUENCY(IF($D$6:$D$65=0,ROW($D$6:$D$65)),IF($D$6:$D$65<>0,ROW($D$6:$D$65)))=F6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Ah, you're right, please add the blue line:

        Do While va(i + 1, 1) = 0
        x = x + 1: i = i + 1
        [COLOR=#0000ff]If i = n Then Exit Do[/COLOR]
        Loop

Akuini, thank you very much for your kind supports it is working flawless!</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Akuini, it solved with 0 & 1 but stuck once again under this situation when before 0 or 1 is number, like 1, 2 & 3 instead 0, 0 & 0</SPAN></SPAN>

Book1
	A	B	C	D
1
2
3
4
5				Of 1's
6				0
7				1
8				2
9				3
10				4
11				0
12				1
13				2
14				0
15				0
16				0
17				1
18				0
19				0
20				1
21				0
22				0
23				1
24				0
25				1
26				0
27				1
28				0
29				0
30				0
31				0
32				0
33				0
34				1
35				0
36				1
37				0
38				1
39				2
40				3
41				0
42				1
43				2
44				3
45				4
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55				0
56				1
57				2
58				3
59				0
60				1
61				0
62				1
63				2
64				3
65				0
Answer1

Please can you check it?</SPAN></SPAN>

Thank you </SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Akuini]

Ah, you're right.
Try this one:

        Do While va(i + 1, 1) = "0"
        x = x + 1: i = i + 1
        If i >= n Then Exit Do
        Loop

 

[motilulla]

Ah, you're right.
Try this one:

        Do While va(i + 1, 1) = "0"
        x = x + 1: i = i + 1
        If i >= n Then Exit Do
        Loop

Akuini, Cheers! all worked fine. Thank you so much for your patience and time you took to help me.</SPAN></SPAN>

Kind Regards</SPAN>
Moti </SPAN></SPAN>