Format for Thousands

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

How can I Adapt ...

    Set Rng = Rng.Cells(Rng.Rows.Count, 1).Offset(1, -1)
    For x = 0 To 6
        Sh.Cells(r, 8).Value = x & "," & 6 - x
        Sh.Cells(r, 9).FormulaR1C1 = "=COUNTIF(R" & First & "C:R" & Last & "C,RIGHT(RC[-1]))"
        r = r + 1
    Next x
    Sh.Cells(r, 8).Value = "Total"
    Sh.Cells(r, 9).Resize(1, 2).FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"

... so that it Produces the Figures with Thousand Seperators Please.

Thanks in Advance.
All the Best.
SHADO

 

[Scott Huish]

You may need to adjust this for the exact format you want, but perhaps something like this:

Sh.Cells(r, 9).Resize(1, 2).NumberFormat = "#,###"

 

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

Thanks HOTPEPPER, it Works Great.

All the Best.
SHADO

 

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

Hi HOTPEPPER,

One More Question Please.

This …

        Sh.Cells(r, 8).Value = x & "," & 6 - x

… Produces this …

0,6
1,5
2,4
3,3
4,2
5,1
6,0

How can I Still get it to Pick Up the Right Hand Figure if I Use …

        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"

… in the Code Below Please …

    Set Rng = Rng.Cells(Rng.Rows.Count, 1).Offset(1, -1)
    For x = 0 To 6
        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"
        Sh.Cells(r, 10).FormulaR1C1 = "=COUNTIF(R" & First & "C:R" & Last & "C,RIGHT(RC[-1]))"
        Sh.Cells(r, 10).NumberFormat = "#,##0"
        r = r + 1
    Next x
    Sh.Cells(r, 9).Value = "Total"
    Sh.Cells(r, 10).FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
    Sh.Cells(r, 10).NumberFormat = "#,##0"

Thanks in Advance.
All the Best.
SHADO

 

[Scott Huish]

Not sure what you mean exactly. What result are you trying to get?

 

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

Hi HOTPEPPER,

Thanks for the Reply.
I have Adapted this Code from Something Else.
I Don't like Using the R1C1 Style But I do Not Know How to Change it.
Here is the Full Code.
It Basically Calculates the Total Odd & Even Numbers for Each Row and Produces a Total.
Then at the Bottom it Totals them Up Depending on the Figures in Column "J" and the Right Figure in the …

        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"

… Piece of Code.

Option Explicit
Option Base 1

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim r As Long
    Dim First As Integer
    Dim Last As Long
    Dim x As Integer
    Set Sh = Worksheets("Draws")
    Set Rng = Sh.Range("I2:I" & Sh.Range("B65536").End(xlUp).Row)
    With Rng
        .FormulaR1C1 = "=SUMPRODUCT(--(MOD(RC[-7]:RC[-2],2)=1))"
        .Offset(0, 1).FormulaR1C1 = "=SUMPRODUCT(--(MOD(RC[-8]:RC[-3],2)=0))"
    End With
    First = Rng.Cells(1, 1).Row
    Last = First + Rng.Rows.Count - 1
    r = Last + 2
    Set Rng = Rng.Cells(Rng.Rows.Count, 1).Offset(1, -1)
    For x = 0 To 6
        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"
        Sh.Cells(r, 10).FormulaR1C1 = "=COUNTIF(R" & First & "C:R" & Last & "C,RIGHT(RC[-1]))"
        Sh.Cells(r, 10).NumberFormat = "#,##0"
        r = r + 1
    Next x
    Sh.Cells(r, 9).Value = "Total"
    Sh.Cells(r, 10).FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
    Sh.Cells(r, 10).NumberFormat = "#,##0"
End Sub

Is there Easier Code for this.
Thanks in Advance.
All the Best.
SHADO

 

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

Hi HOTPEPPER,

I got Round it by Using ...

        Sh.Cells(r, 10).FormulaR1C1 = "=COUNTIF(R" & First & "C:R" & Last & "C,MID(RC[-1],9,1))"

Can the Code be Simplified in Any Way by Not Using the R1C1 Style Please.

Thanks for All your Help.
All the Best.
SHADO