# Format for Thousands

#### S.H.A.D.O.

##### Well-known Member

Code:
``````    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.

All the Best.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Scott Huish

##### MrExcel MVP
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.

##### Well-known Member
Thanks HOTPEPPER, it Works Great.

All the Best.

#### S.H.A.D.O.

##### Well-known Member
Hi HOTPEPPER,

This …

Code:
``        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 …

Code:
``        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"``
… in the Code Below Please …

Code:
``````    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"``````
All the Best.

#### Scott Huish

##### MrExcel MVP

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

#### S.H.A.D.O.

##### Well-known Member
Hi HOTPEPPER,

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 …

Code:
``        Sh.Cells(r, 9).Value = x & " Odd & " & 6 - x & " Even"``
… Piece of Code.

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.
All the Best.

#### S.H.A.D.O.

##### Well-known Member
Hi HOTPEPPER,

I got Round it by Using ...

Code:
``        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.

All the Best.

Replies
3
Views
236
Replies
1
Views
111
Replies
1
Views
186
Replies
3
Views
509
Replies
5
Views
205