Count Odd & Even Numbers from A Loop

[S.H.A.D.O.]

Good evening,

What I am trying to do is to loop through ALL 6 number combinations and count how many Odd & Even numbers there are in each of the 6 positions.
For example, for number TEN I would like the total combinations where number TEN is Odd in position ONE, then number TEN is Even in position ONE, then number TEN is Odd in position TWO, then number TEN is Even in position TWO etc upto and including where number TEN is Odd in position SIX, then number TEN is Even in position SIX.
Obviously number TEN is Even, but out of the TWELVE columns of data for each of the numbers there will be SIX with a figure in it and SIX showing ZERO.
Here is the code I have so far but can't quite get it to work.
Thanks in advance.

Option Explicit
Option Base 1

Sub Odds_and_Evens_by_Position()
    Dim A As Long, B As Long, C As Long, D As Long, E As Long, F As Long
    Dim OddA As Long, OddB As Long, OddC As Long
    Dim OddD As Long, OddE As Long, OddF As Long
    Dim EvenA As Long, EvenB As Long, EvenC As Long
    Dim EvenD As Long, EvenE As Long, EvenF As Long
    Dim n As Long
    With Sheets("Odd & Even").Select
        Range("B:N").ClearContents
        Range("B2").Select
        For n = 1 To 30
            For A = 1 To 25
                If A Mod 2 = 1 Then OddA = OddA
                If A Mod 2 = 0 Then EvenA = EvenA
                For B = A + 1 To 26
                    If B Mod 2 = 1 Then OddB = OddB
                    If B Mod 2 = 0 Then EvenB = EvenB
                    For C = B + 1 To 27
                        If C Mod 2 = 1 Then OddC = OddC
                        If C Mod 2 = 0 Then EvenC = EvenC
                        For D = C + 1 To 28
                            If D Mod 2 = 1 Then OddD = OddD
                            If D Mod 2 = 0 Then EvenD = EvenD
                            For E = D + 1 To 29
                                If E Mod 2 = 1 Then OddE = OddE
                                If E Mod 2 = 0 Then EvenE = EvenE
                                For F = E + 1 To 30
                                    If F Mod 2 = 1 Then OddF = OddF
                                    If F Mod 2 = 0 Then EvenF = EvenF
                                    OddA = OddA + 1
                                    EvenA = EvenA + 1
                                    OddB = OddB + 1
                                    EvenB = EvenB + 1
                                    OddC = OddC + 1
                                    EvenC = EvenC + 1
                                    OddD = OddD + 1
                                    EvenD = EvenD + 1
                                    OddE = OddE + 1
                                    EvenE = EvenE + 1
                                    OddF = OddF + 1
                                    EvenF = EvenF + 1
                                Next F
                            Next E
                        Next D
                    Next C
                Next B
            Next A
        Next
        For n = 1 To 30
            With ActiveCell
                .Offset(2, 0).Value = n
                .Offset(2, 1).Value = OddA
                .Offset(2, 2).Value = EvenA
                .Offset(2, 3).Value = OddB
                .Offset(2, 4).Value = EvenB
                .Offset(2, 5).Value = OddC
                .Offset(2, 6).Value = EvenC
                .Offset(2, 7).Value = OddD
                .Offset(2, 8).Value = EvenD
                .Offset(2, 9).Value = OddE
                .Offset(2, 10).Value = EvenE
                .Offset(2, 11).Value = OddF
                .Offset(2, 12).Value = EvenF
                .Offset(1, 0).Select
            End With
        Next n
    End With
End Sub

 

[pgc01]

I'm glad it's working as you want.

I know this probably doesn't apply to the columns used for the totals though like 12 & 13.

The 12 is 2 * the number of elements of each combination (in the code it's the variable p), so it's (2 * p). The 13 is 2 * the number of elements of each combination + 1 (next column), in the case of the code (2 * p + 1)

For ex., for

        .Offset(0, 13).Resize(n, 1).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"

you could use

        .Offset(0, 2 * p + 1).Resize(n, 1).FormulaR1C1 = "=SUM(RC[-" & (2 * p) & "]:RC[-1])"

 

[S.H.A.D.O.]

The 12 is 2 * the number of elements of each combination (in the code it's the variable p), so it's (2 * p). The 13 is 2 * the number of elements of each combination + 1 (next column), in the case of the code (2 * p + 1)

For ex., for

        .Offset(0, 13).Resize(n, 1).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"

you could use

        .Offset(0, 2 * p + 1).Resize(n, 1).FormulaR1C1 = "=SUM(RC[-" & (2 * p) & "]:RC[-1])"

Thanks.
I can't get my head round the column totals though, I am being a bit slow today!
I tried this...

        .Offset(0, n + 1).Resize(n, 1).FormulaR1C1 = "=SUM(RC[-" & (n) & "]:RC[-1])"

...among others.

 

[pgc01]

Hi

I think the formula is ok, you really want to add the n cells above.

The location and size of the range where the formula is applied is, however, wrong.

You want to start the range below the table, n rows down and 1 column to the right.

Instead of

.Offset(0, n + 1)

shoud be

.Offset(n, 1)

you want the formulas in the same row ( 1 row) spanning 12 columns, or (2 * p), for the odd and even.

Instead of

.Resize(n, 1)

should be

.Resize(1, 2 * p)

So, I'd try:

.Offset(n, 1).Resize(1, 2 * p).FormulaR1C1 = "=SUM(RC[-" & (n) & "]:RC[-1])"

 

[S.H.A.D.O.]

Thanks for the reply pgc01,

So, I'd try:

.Offset(n, 1).Resize(1, 2 * p).FormulaR1C1 = "=SUM(RC[-" & (n) & "]:RC[-1])"

That didn't actually work so I had a play around with it and came up with...

        .Offset(n, 1).Resize(1, 2 * p).FormulaR1C1 = "=SUM(R[-" & (n) & "]C:R[-1]C)"

...which works great.
Thanks for all you help and time on this, it is appreciated.

 

[pgc01]

I'm glad you figured it out. Cheers.