vba Odd's and Even"s Array Count If

[montecarlo2012]

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"),

 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

	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,

  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.

 

[Rick Rothstein]

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]

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]

How about

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]

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]

How about

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]

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]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

How about

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]"