Hi Tom,

That’s Great, Thanks Very Much.
I Setup a rng1 and have Added the Grand Total ( nTypeTotal1 ) for the
Groups as Shown in the Snippet Below, is this the Best Way to do this.

nTypeTotal1 = ActiveCell.Row

For i = 1 To 5
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = v(i)
rng(2 + i, 1).Value = nType1(i)
rng(2 + i, 1).NumberFormat = "#,##0"
Next

ActiveCell.Offset(8, 0).Value = "Grand Total"

Set rng1 = ActiveCell.Offset(8, 2)
ActiveCell.Offset(8, 2).NumberFormat = "#,##0"
rng1.FormulaR1C1 = "=Sum(R" & nTypeTotal1 & "C:R[-1]C)"
rng1.Formula = rng.Value

The Bit that I think Could be Improved is the 3 Lines where the Offset
is Set to 8. As it is, I would have to Change the 8 if I was to Add Or
Reduce the Number of Groups. I Cannot Seem to get it to be as Flexible
as the Original Grand Total where it Doesn't Matter How Many Values
there are, it Still Puts the Total Directly Underneath.
I Tested the Macro Out and Found that it Doesn't like it if One Or More
of the Groups is NOT 20 Numbers.
Also, what does the 2 in "rng(2 +" Bit do Please.

Thanks Again.
All the Best.
Paul



Help with Totalling Groups Please
From: Tom Ogilvy

Option Base 1

Sub Test()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim nType(203) As Long
Dim nTypeTotal As Long
Dim rng As Range
Dim s As Long, s1 As Long
Dim ntype1(5) As Long
Dim v As Variant
v = Array("103 to 122", _
"123 to 142", _
"143 to 162", _
"163 to 182", _
"183 to 203")

Application.ScreenUpdating = False
Sheets("Results").Select
Range("B3").Select

For i = 103 To 203
nType(i) = 0
Next i

For A = 1 To 33 - 5
For B = A + 1 To 33 - 4
For C = B + 1 To 33 - 3
For D = C + 1 To 33 - 2
For E = D + 1 To 33 - 1
For F = E + 1 To 33
s1 = A + B + C + D + E + F
If s1 < 203 Then
s = Int((s1 - 103) / 20) + 1
Else
s = 5
End If
nType(s1) = nType(s1) + 1
If s 0 Then
ntype1(s) = ntype1(s) + 1
End If
Next F
Next E
Next D
Next C
Next B
Next A

nTypeTotal = ActiveCell.Row

For i = 103 To 203
ActiveCell.Offset(1, 0).Value = "Total for"
ActiveCell.Offset(1, 1).Value = i
ActiveCell.Offset(1, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i

ActiveCell.Offset(1, 0).Value = "Grand Total"

Set rng = ActiveCell.Offset(1, 2)
rng.FormulaR1C1 = "=Sum(R" & nTypeTotal & "C:R[-1]C)"
rng.Formula = rng.Value

For i = 1 To 5
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = v(i)
rng(2 + i, 1).Value = ntype1(i)
rng(2 + i, 1).NumberFormat = "#,##0"
Next
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy





*** Sent via Developersdex http://www.developersdex.com ***