coun problem again

  • Thread starter Thread starter muumigirl
  • Start date Start date
M

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
 
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)
 
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 :
 
Back
Top