Format for Thousands

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
How can I Adapt ...


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.

Thanks in Advance.
All the Best.
SHADO
 

Some videos you may like

Excel Facts

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Sep 6, 2005
Messages
1,915
Hi HOTPEPPER,

One More Question Please.

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"
Thanks in Advance.
All the Best.
SHADO
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
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 …


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.
Thanks in Advance.
All the Best.
SHADO
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
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.

Thanks for All your Help.
All the Best.
SHADO
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top