counting

[montecarlo2012]

Hello.
I have a little snipe code able to tell me how many times number 1 is in the column A, I am trying to count how many time 2 is after one, but still I don't know how to say that in VBA. This is the little code:

     Sub countmontecarlo()Dim cell As Range
Dim n As Long
      For Each cell In Range("A:A")
            If cell = 1 Then
          
            n = n + 1
            End If
      Next cell
Range("b6").Value = n
End Sub

the code count 6 one's but how to count 5 times 2 under the coordinate 1-2, thank you.

[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

 

[DanteAmor]

As you can see, at the beginning the post has a little Matrix, what I was trying to request is to count the value of following position for each number. I don't know if make sence,
like how many times 3 is after 2, and so on, but I really apreciate the answers, I am working now trying to find the way, for me is really dificult, but....

List and resulting matrix

Book1
	A	B	C	D	E	F	G	H
1	1			1	2	3	4	5
2	2		1	0	2	3	0	0
3	3		2	5	0	0	0	0
4	1		3	1	3	0	0	0
5	2		4	0	0	1	0	0
6	3		5	0	0	0	1	0
7	1
8	2
9	3
10	1
11	2
12	1
13	2
14	1
15	3
16	4
17	5
Sheet1

Try this (With the formula sum, Rick's suggestion)

Sub counting_n()
    Dim r As Range, wMin As Double, wMax As Double, wRow As Long, wCol As Long, i As Long, j As Long
    
    Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
    wMin = WorksheetFunction.Min(r)
    wMax = WorksheetFunction.Max(r)
    r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
    
    wRow = 2
    For i = wMin To wMax
        wCol = 4
        Cells(wRow, "C").Value = i
        For j = wMin To wMax
            Cells(1, wCol).Value = j
            Cells(wRow, wCol).Value = Evaluate("SUM((" & r.Address & "=" & j & ")*(" & r.Offset(1).Address & "=" & i & "))")
            wCol = wCol + 1
        Next
        wRow = wRow + 1
    Next
    MsgBox "Done"
End Sub

 

[DanteAmor]

Another way without using loop

Sub counting_2()
    Dim r As Range, wMin As Double, wMax As Double
    
    Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
    wMin = WorksheetFunction.Min(r)
    wMax = WorksheetFunction.Max(r)
    r.Offset(0, 2).Resize(r.Count, wMax + 1).ClearContents
    Range("D1, C2") = wMin
    Range("D1").AutoFill Destination:=Range("D1").Resize(, wMax), Type:=xlFillSeries
    Range("C2").AutoFill Destination:=Range("C2").Resize(wMax), Type:=xlFillSeries
    
    With Range("D2").Resize(wMax, wMax)
        .Formula = "=SUMPRODUCT((R1C1:R" & r.Count & "C1=R1C)*(R2C1:R" & r.Count + 1 & "C1=RC3))"
        .Value = .Value
    End With
    MsgBox "Done"
End Sub

 

[montecarlo2012]

Thank you DanteAmor, You are really Good, great job.

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.