coun problem again

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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 have almost 60 000 entries and that makes the whole processing too slow



Thank You, Necessitysslave, that code works