counting
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: counting

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default counting

    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:
    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.
    1 1 2 3
    2 1
    3 2 5
    1 3
    2
    3 6
    1
    2
    3
    1
    2
    1
    2
    1
    3

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    Code:
    Sub countmontecarlo()
    Dim cell As Range
    Dim lastrow As Long
    Dim n As Long
    Dim i As Long
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            For i = 1 To lastrow
        
                If .Cells(i, "A").Value = 1 And .Cells(i + 1, "A").Value = 2 Then
                    
                    n = n + 1
                End If
            Next i
            .Range("B6").Value = n
        End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    Thank you So much theBardd. worked perfect.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,691
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: counting

    How about:

    Code:
    Sub counting1_2()
        Dim r As Range
        Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
        Range("B5").Value = WorksheetFunction.CountIf(r, 1)
        Range("B6").Value = Evaluate("=SUMPRODUCT((" & r.Address & "=1)*(" & r.Offset(1).Address & "=2))")
    End Sub
    Regards Dante Amor

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,091
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: counting

    Quote Originally Posted by DanteAmor View Post
    How about:

    Code:
        Range("B6").Value = Evaluate("=SUMPRODUCT((" & r.Address & "=1)*(" & r.Offset(1).Address & "=2))")
    Since the Evaluate function processes non-text functions as arrays (when an array of values are involved) natively, you can use the SUM function instead of the SUMPRODUCT function for the expression you are giving it. Also, you do not need to include the equal sign for the Evaluate function.
    Code:
    Range("B6").Value = Evaluate("SUM((" & r.Address & "=1)*(" & r.Offset(1).Address & "=2))")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    Thank you DanteAmor.

  7. #7
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    Mr. Rick Rothstein My personal admiration for you Sir, Thank you, So much for your input.

  8. #8
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    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....

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,691
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: counting

    Quote Originally Posted by montecarlo2012 View Post
    Thank you DanteAmor.
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  10. #10
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: counting

    DanteAmor you are always very helpfull thank you So much.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •