vba Odd's and Even"s Array Count If
Results 1 to 9 of 9

Thread: vba Odd's and Even"s Array Count If

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

    Default vba Odd's and Even"s Array Count If

    Hello.
    I have a dynamic array B2:G2600 (6 columns), on Col "M" would like to see how many odd numbers there are in the rows, and on Col "N" the even numbers.
    So I workout first with the macro recorder, ("this is a short version as example, only"),
    Code:
     Sub CORR_ECT()Range("H2").Select
       Selection.FormulaArray = "=COUNT(IF(MOD(A2:F2,2)=0,A2:H2))" ''''EVENS
            Range("I2").Select
    Selection.FormulaArray = "= COUNT(IF(MOD(A2:F2,2),A2:I2))"  '''''ODDS
    End Sub
    and the example data
    Code:
     
    B C D E F G ODD EVEN
    2 1 2 3 4 5 6 3 3
    3 7 8 9 10 11 12
    4 13 14 15 16 17 18
    5 19 20 21 22 23 24
    6 25 26 27 28 29 30
    7 31 32 33 34 35 36
    8 37 38 39 40 41 42
    9 43 44 45 46 47 48
    So I am trying to loop this array, but of course do not work, this is my code,
    Code:
      Sub OD_EV()      Dim X As Integer, J As Integer
          finalRow = Cells(Row.Count, 2).End(xlUp).Row
                   For X = 2 To finalRow
                   For J = 1 To 6
                            Range("H" & X) = FormulaArray = "=COUNT(IF(MOD(X:J,2)=0,X:J2))" ''''EVENS
                            Range("I" & X) = FormulaArray = "=COUNT(IF(MOD(X:J,2),X:J2))" 'ODDS
                   Next J
                   Next X
    End Sub
    Thank you for reading this.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,956
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: vba Odd's and Even"s Array Count If

    Are you required to use VBA to solve this? If not, you can put these formulas in the indicated cells and copy them down to the end of your data...

    M2: =SUMPRODUCT(--(MOD(B2:G2,2)=1))

    N2: =SUMPRODUCT(--(MOD(B2:G2,2)=0))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  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: vba Odd's and Even"s Array Count If

    Thank you, Rick.
    Yes I need to see the code.
    Its the not the Destination, It's the journey
    I really learn more from the code, the logic behind, the transformation from English to VBA is what count most
    Thanks.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,381
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: vba Odd's and Even"s Array Count If

    How about
    Code:
    Sub montecarlo2012()
       Dim Ary As Variant
       Dim r As Long, c As Long, Ev As Long, Od As Long
       
       Ary = Range("B2", Range("B" & Rows.Count).End(xlUp).Offset(, 7)).Value2
       
       For r = 1 To UBound(Ary)
          For c = 1 To UBound(Ary, 2) - 2
             If Application.IsEven(Ary(r, c)) Then Ev = Ev + 1 Else Od = Od + 1
          Next c
          Ary(r, 7) = Od: Ary(r, 8) = Ev
          Od = 0: Ev = 0
       Next r
       Range("B2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: vba Odd's and Even"s Array Count If

    Thank you Fluff (MrExcel MVP).
    Is really Nice from you to give me this code. Important lesson I got here, first I try to change the result locations, so when I see Ary(r, 7) my Visual Logic thought about, Col H if vba count from "0" this is 7, and 8, then when I change to 13 or Col M that is the place I really need this, nothing happen, then I saw the line Offset(, 7), I was thinking here it is, nothing either, So here I am, my question here is what to change in order to move the results to Column ("M2") and ("N2"). PLEASE.
    I can see Ary is like finalRow = etc. the rest I have to study more,
    I am really impress you named the sub as montecarlo, thank you,
    Great Lesson.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,381
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: vba Odd's and Even"s Array Count If

    How about
    Code:
    Sub montecarlo2012()
       Dim Ary As Variant
       Dim r As Long, c As Long, Ev As Long, Od As Long
       
       Ary = Range("B2", Range("B" & Rows.Count).End(xlUp).Offset(, 12)).Value2
       
       For r = 1 To UBound(Ary)
          For c = 1 To UBound(Ary, 2) - 7
             If Application.IsEven(Ary(r, c)) Then Ev = Ev + 1 Else Od = Od + 1
          Next c
          Ary(r, 12) = Od: Ary(r, 13) = Ev
          Od = 0: Ev = 0
       Next r
       Range("B2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  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: vba Odd's and Even"s Array Count If

    THANK YOU SIR. You are really Nice, Thank you for your time. work perfect, I See you highlight the changes, wow. I hope you have a great day.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,381
    Post Thanks / Like
    Mentioned
    440 Post(s)
    Tagged
    45 Thread(s)

    Default Re: vba Odd's and Even"s Array Count If

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,956
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: vba Odd's and Even"s Array Count If

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub montecarlo2012()
       Dim Ary As Variant
       Dim r As Long, c As Long, Ev As Long, Od As Long
       
       Ary = Range("B2", Range("B" & Rows.Count).End(xlUp).Offset(, 12)).Value2
       
       For r = 1 To UBound(Ary)
          For c = 1 To UBound(Ary, 2) - 7
             If Application.IsEven(Ary(r, c)) Then Ev = Ev + 1 Else Od = Od + 1
          Next c
          Ary(r, 12) = Od: Ary(r, 13) = Ev
          Od = 0: Ev = 0
       Next r
       Range("B2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
    End Sub
    Just noting as a point of interest that as an alternative, what I highlighted in red can be replaced with this...

    Ary(r, c) Like "*[02468]"
    Last edited by Rick Rothstein; Jun 15th, 2019 at 12:01 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •