i have numbers 1 and 0 in column a, i would need to know how many 0 are
between ones. how could i find it out? this 1 and o sequence is very
long
A B
1 1
2 0
3 1 1
4 0
5 0
6 1 2
7 0
8 0
9 0
10 1 3
Thanks


--
muumigirl
------------------------------------------------------------------------
muumigirl's Profile: http://www.excelforum.com/member.php...o&userid=30674
View this thread: http://www.excelforum.com/showthread...hreadid=503314

You could try something like this...

Sub CountZeros()

Dim intStartCounter as integer
Dim intCounter as integer

intstartcounter = 1
intCounter = 1

do while len(range("B" & intCounter).text) < 0

do while range("B" & intCounter).text < 1

intcounter = intcounter + 1

loop

range("C" & intcounter).value = intcounter - (intstartCounter + 1)

intStartCounter = intcounter

Loop

I haven't tested this and wrote it directly here, so please check syntax.
Also, this assumes that the data is in column B and starts in row 1. If this
is wrong, you'll need to alter this in the code above.

HTH.

"muumigirl" wrote:


i have numbers 1 and 0 in column a, i would need to know how many 0 are
between ones. how could i find it out? this 1 and o sequence is very
long
A B
1 1
2 0
3 1 1
4 0
5 0
6 1 2
7 0
8 0
9 0
10 1 3
Thanks


--
muumigirl
------------------------------------------------------------------------
muumigirl's Profile: http://www.excelforum.com/member.php...o&userid=30674
View this thread: http://www.excelforum.com/showthread...hreadid=503314

Put this formula in B2

=IF(A2=0,"",COUNTIF(INDIRECT("A"&MAX(IF($A$1:A1=1, ROW($A$1:A1)))&":A"&ROW()-
1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter. Then copy B2 down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"muumigirl" wrote
in message ...

i have numbers 1 and 0 in column a, i would need to know how many 0 are
between ones. how could i find it out? this 1 and o sequence is very
long
A B
1 1
2 0
3 1 1
4 0
5 0
6 1 2
7 0
8 0
9 0
10 1 3
Thanks


--
muumigirl
------------------------------------------------------------------------
muumigirl's Profile:
http://www.excelforum.com/member.php...o&userid=30674
View this thread: http://www.excelforum.com/showthread...hreadid=503314