I'm still in trouble with my count problem. The problem was this:

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

I now have the code, that unfortunately doesn't work correctly. Becaus
i'm a beginner in visual basic, i don't know how to fix it.
The code is:
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

End Sub

I debugged it and added watches, and based to that I understood tha
the program calculates only the first cycle. After that the progra
continues the cycle but does not calculate.

How could I make the program do the calculations and find the result
for the whole row?

Can anyone Help Please?
Thanks
Muumigir

--
muumigir
-----------------------------------------------------------------------
muumigirl's Profile: http://www.excelforum.com/member.php...fo&userid=3067
View this thread: http://www.excelforum.com/showthread.php?threadid=50438

I'll try my formula again

enter this in B2 and copy down

=IF(B2=0,"",
COUNTIF(INDIRECT("B"&MAX(IF($B$1:B1=1,ROW($B$1:B1) ))&":B"&ROW()-1),0))

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"muumigirl" wrote
in message ...

I'm still in trouble with my count problem. The problem was this:

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
__B C
1 1
2 0
3 1 1
4 0
5 0
6 1 2
7 0
8 0
9 0
10 1 3

I now have the code, that unfortunately doesn't work correctly. Because
i'm a beginner in visual basic, i don't know how to fix it.
The code is:
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

End Sub

I debugged it and added watches, and based to that I understood that
the program calculates only the first cycle. After that the program
continues the cycle but does not calculate.

How could I make the program do the calculations and find the results
for the whole row?

Can anyone Help Please?
Thanks
Muumigirl


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

If I understand you correctly then try the following

Sub CountZeros()

Dim Zeros As Integer
Dim Row As Integer

Zeros = 0
Row = 1
With Worksheets(1)
Do
If .Range("B" & Row) < 0 Then
Do
Row = Row + 1
Zeros = Zeros + 1
Loop While .Range("B" & Row) = 0
End If
.Range("C" & Row) = Zeros - 1
Zeros = 0
Loop While .Range("B" & Row + 1) < ""
End With
End Sub

should work

Thank You, Bob Philips, but I can't use a simple formula, because I hav
almost 60 000 entries and that makes the whole processing too slow



Thank You, Necessitysslave, that code works :

--
muumigir
-----------------------------------------------------------------------
muumigirl's Profile: http://www.excelforum.com/member.php...fo&userid=3067
View this thread: http://www.excelforum.com/showthread.php?threadid=50438