coun problem again

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

muumigirl - 24 Jan 2006 10:00 GMT

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

Reply to this Message

Bob Phillips - 24 Jan 2006 10:38 GMT

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)

> I'm still in trouble with my count problem. The problem was this:
>
[quoted text clipped - 56 lines]
> muumigirl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30674
> View this thread: http://www.excelforum.com/showthread.php?threadid=504388

Reply to this Message

Necessitysslave - 24 Jan 2006 10:40 GMT

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

Reply to this Message

muumigirl - 24 Jan 2006 11:00 GMT

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 :

Reply to this Message