Counting number

[straw]

Dear all,

I am trying to write a VBA code for statistics purpose in EXCEL. For example:

(Given)---space---(Count by VBA)
Statistics---space---Binomial Times
1 <<Box A1---space--- 3 (means there are "1" X 3) <<BOX B2
1 -------space---------1<<in red text (means there is "0" X 1) <<BOX B3
1 -------space---------1 (means there is "1" X 1) <<BOX B4
0 -------space---------4 <<in red text (means there are "0" X 4)
1 -------space---------2 (means there are "2" X 2)
0 -------space---------and so on
0
0
0
1
1
and so on
=================================================

List (BOX D2) = all "1" Times ( black text in colume B)
List (BOX E3) = all "0" Times (red text in colume B)

I know it's too complicated and I don't know if it is possible to do this redundant steps by VBA code. Since I am still at the very beginning level of VBA and I have no idea of how I can do this, could someone please help me at this problem?


Thank you very much.

[AnAnalyst]

So you want to count the "runs" within binomial data?

Using a blank worksheet, right click on the sheet tab and click "View Code".

Copy the following code into the VBA window:

[VBA]Sub RunsFrequency()
Dim i As Long
Dim j As Long
Dim lr As Long
Dim k As Integer
Dim headers()
With ActiveSheet
headers = Array("Data", "Run Length", "Binomial", "Result", "Count of Runs")
.Cells(1, 1).Resize(1, 5).Value = headers
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(2, 2).Resize(Rows.Count - 1, 5).Clear
.Cells(2, 4).Value = 1
.Cells(3, 4).Value = 0
j = 2
For i = 2 To lr
k = .Cells(i, 1).Value
.Cells(j, 3).Value = k
.Cells(j, 2).Value = .Cells(j, 2).Value + 1
If k = 0 Then .Cells(j, 2).Font.Color = RGB(255, 0, 0)
If .Cells(i + 1, 1).Value <> k Then
j = j + 1
End If
Next i
.Cells(2, 5).Resize(2, 1).Formula = "=countif($C$2:$C$" & j & ",d2)"
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("a2:a" & Rows.Count)) Is Nothing Then
RunsFrequency
End If
End Sub[/VBA]

Now, when you enter binomial data into the worksheet in column A starting at row 2 the runs of 1's and 0's will be checked and counted.

Is this roughly what you wanted?

http://www.vbaexpress.com/forum/atta...1&d=1324174485

[straw]

Dear AnAnalyst,

Thank you so much for your help first. This is really close to what I want. But I want to count the "number of runs" in total before it turns to the other side (e.g. 0,0,0,1,0,0,1,1,0,1 then the "number of runs" will show 3 (red),1,2 (red),2,1 (red),1).

I had tried to attach an image to this thread but no luck. Sorry for my poor English : <

Hopefully you can understand what I meant.

Again, I really appreciate your help.

Straw

[straw]

Dear AnAnalyst,

Sorry, my bad. Your codes work perfectly. Before I forgot to change the activesheet, now it works really great. Thank you very much.

Based on your code, I just need to make some handily change, then it will fit my purpose. I will try to study your code for future use. Thanks again.

Straw

[AnAnalyst]

You're welcome, Straw - and your English is 100% better than any other language that I try to speak! If you need anything explaining in the code or any further help then just ask,

Kind regards, AnAnalyst

[straw]

Dear AnAnalyst,

You are really kind, thank you very much. In fact, I found that it is very difficlut to understand your code, but I am trying to understand/study it first by myself (I can't always rely on you). If I encounter something I can't understand, I will definitely ask you.

Your help is highly appreciated. Thank you very much.

Best reagrds,
Straw