coun problem again

[muumigirl]

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

 

[Bob Phillips]

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)

 

[Necessitysslave]

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

 

[muumigirl]

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 :