vba Odd's and Even"s Array Count If

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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:
 [TABLE="width: 792"]
 <colgroup><col width="88" span="9" style="width:66pt"> </colgroup><tbody>[TR]
  [TD="class: xl65, width: 88"] [/TD]
  [TD="class: xl66, width: 88"]B[/TD]
  [TD="class: xl66, width: 88"]C[/TD]
  [TD="class: xl66, width: 88"]D[/TD]
  [TD="class: xl66, width: 88"]E[/TD]
  [TD="class: xl66, width: 88"]F[/TD]
  [TD="class: xl66, width: 88"]G[/TD]
  [TD="class: xl66, width: 88"]ODD[/TD]
  [TD="class: xl66, width: 88"]EVEN[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]1[/TD]
  [TD="class: xl65, align: right"]2[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]3[/TD]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]10[/TD]
  [TD="class: xl65, align: right"]11[/TD]
  [TD="class: xl65, align: right"]12[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]4[/TD]
  [TD="class: xl65, align: right"]13[/TD]
  [TD="class: xl65, align: right"]14[/TD]
  [TD="class: xl65, align: right"]15[/TD]
  [TD="class: xl65, align: right"]16[/TD]
  [TD="class: xl65, align: right"]17[/TD]
  [TD="class: xl65, align: right"]18[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]5[/TD]
  [TD="class: xl65, align: right"]19[/TD]
  [TD="class: xl65, align: right"]20[/TD]
  [TD="class: xl65, align: right"]21[/TD]
  [TD="class: xl65, align: right"]22[/TD]
  [TD="class: xl65, align: right"]23[/TD]
  [TD="class: xl65, align: right"]24[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]6[/TD]
  [TD="class: xl65, align: right"]25[/TD]
  [TD="class: xl65, align: right"]26[/TD]
  [TD="class: xl65, align: right"]27[/TD]
  [TD="class: xl65, align: right"]28[/TD]
  [TD="class: xl65, align: right"]29[/TD]
  [TD="class: xl65, align: right"]30[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]7[/TD]
  [TD="class: xl65, align: right"]31[/TD]
  [TD="class: xl65, align: right"]32[/TD]
  [TD="class: xl65, align: right"]33[/TD]
  [TD="class: xl65, align: right"]34[/TD]
  [TD="class: xl65, align: right"]35[/TD]
  [TD="class: xl65, align: right"]36[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]8[/TD]
  [TD="class: xl65, align: right"]37[/TD]
  [TD="class: xl65, align: right"]38[/TD]
  [TD="class: xl65, align: right"]39[/TD]
  [TD="class: xl65, align: right"]40[/TD]
  [TD="class: xl65, align: right"]41[/TD]
  [TD="class: xl65, align: right"]42[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
 [/TR]
 [TR]
  [TD="class: xl65, align: right"]9[/TD]
  [TD="class: xl65, align: right"]43[/TD]
  [TD="class: xl65, align: right"]44[/TD]
  [TD="class: xl65, align: right"]45[/TD]
  [TD="class: xl65, align: right"]46[/TD]
  [TD="class: xl65, align: right"]47[/TD]
  [TD="class: xl65, align: right"]48[/TD]
  [TD="class: xl65"] [/TD]
  [TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,028
Office Version
2010
Platform
Windows
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))
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,400
Office Version
365
Platform
Windows
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
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,400
Office Version
365
Platform
Windows
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(, [COLOR=#ff0000]12[/COLOR])).Value2
   
   For r = 1 To UBound(Ary)
      For c = 1 To UBound(Ary, 2) - [COLOR=#ff0000]7[/COLOR]
         If Application.IsEven(Ary(r, c)) Then Ev = Ev + 1 Else Od = Od + 1
      Next c
      Ary(r, [COLOR=#ff0000]12[/COLOR]) = Od: Ary(r, [COLOR=#ff0000]13[/COLOR]) = Ev
      Od = 0: Ev = 0
   Next r
   Range("B2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
519
Office Version
2010
Platform
Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,400
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,028
Office Version
2010
Platform
Windows
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(, [COLOR=#ff0000]12[/COLOR])).Value2
   
   For r = 1 To UBound(Ary)
      For c = 1 To UBound(Ary, 2) - 7
         If [B][COLOR="#FF0000"]Application.IsEven(Ary(r, c))[/COLOR][/B] 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:

Watch MrExcel Video

Forum statistics

Threads
1,101,887
Messages
5,483,516
Members
407,397
Latest member
HerbA

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top