Help with Totalling Groups Please

P

Paul Black

Hi,

The Macro Below Produces a Total ( nType ) and Grand Total ( nTypeTotal
) Related to Variable i.
I would However, like to Add at the Bottom ( Starting 2 Rows Down from
the Already Produced Grand Total in the Macro ) the Totals for the
Following Groups :-

Total for 103 to 122 = 351,106
Total for 123 to 142 = 160,900
Total for 143 to 162 = 30,227
Total for 163 to 182 = 1,512
Total for 183 to 203 = 1
Grand Total = 543,746

Here is the Macro :-

Option Explicit
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

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

nType(A + B + C + D + E + F) = nType(A + B + C + D + E + F) + 1

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

Application.ScreenUpdating = True
End Sub

I have Tried Adapting "=Sum(R" & nTypeTotal & "C:R[-1]C)" which Works (
Thanks to Tom Ogilvy & Bob Phillips ) for the Grand Total in the Macro
Above But to NO Avail.
Is this Possible Using Code like the nTypeTotal to Output the Values
Instead of the Code Entering Formulas into the Worksheet Please.

Any Help will be Greatly Appreciated.
Thanks in Advance.
All the Best.
Paul




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

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

Paul Black said:
Hi,

The Macro Below Produces a Total ( nType ) and Grand Total ( nTypeTotal
) Related to Variable i.
I would However, like to Add at the Bottom ( Starting 2 Rows Down from
the Already Produced Grand Total in the Macro ) the Totals for the
Following Groups :-

Total for 103 to 122 = 351,106
Total for 123 to 142 = 160,900
Total for 143 to 162 = 30,227
Total for 163 to 182 = 1,512
Total for 183 to 203 = 1
Grand Total = 543,746

Here is the Macro :-

Option Explicit
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

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

nType(A + B + C + D + E + F) = nType(A + B + C + D + E + F) + 1

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

Application.ScreenUpdating = True
End Sub

I have Tried Adapting "=Sum(R" & nTypeTotal & "C:R[-1]C)" which Works (
Thanks to Tom Ogilvy & Bob Phillips ) for the Grand Total in the Macro
Above But to NO Avail.
Is this Possible Using Code like the nTypeTotal to Output the Values
Instead of the Code Entering Formulas into the Worksheet Please.

Any Help will be Greatly Appreciated.
Thanks in Advance.
All the Best.
Paul




*** Sent via Developersdex http://www.developersdex.com ***
Click to expand...
 
P

Paul Black

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



Re: 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 ***
 
T

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 nType(203) As Long
Dim nTypeTotal As Long
Dim rng As Range
Dim s As Long, s1 As Long
Dim grpsize As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range
Dim i As Long, j As Long, k As Long
Dim l As Long

'
' set group size
'
grpsize = 20


Application.ScreenUpdating = False
Sheets("Results").Select
Range("B3").Select
Columns("B:F").ClearContents

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
nType(s1) = nType(s1) + 1
Next F
Next E
Next D
Next C
Next B
Next A

nTypeTotal = ActiveCell.Row
Set rng1 = ActiveCell.Offset(1, 2)
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
Set rng2 = ActiveCell.Offset(0, 2)
ActiveCell.Offset(1, 0).Value = "Grand Total"
Set rng3 = Range(rng1, rng2)
Set rng = ActiveCell.Offset(1, 2)
rng.Value = Application.Sum(rng3)

ActiveCell.Offset(2, 0).Select
k = Application.RoundUp(rng3.Count / grpsize, 0)

j = 103
Set rng4 = rng(2 + 1, 1)
Set rng5 = rng(2 + k, 1)
Set rng6 = Range(rng4, rng5)
For i = 1 To k
l = j + grpsize - 1
If l > rng2.Offset(0, -1).Value Then
l = rng2.Offset(0, -1).Value
End If
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = j & " to " & l
rng(2 + i, 1).Value = Application.SumIf( _
rng3.Offset(0, -1), ">=" & j, rng3) - _
Application.SumIf(rng3.Offset(0, -1), _
">" & l, rng3)
rng(2 + i, 1).NumberFormat = "#,##0"
j = l + 1
Next

rng5.Offset(2, -2).Value = "Grand Total"
rng5.Offset(2, 0).NumberFormat = "#,##0"
rng5.Offset(2, 0).Value = Application.Sum(rng6)



Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

Paul Black said:
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



Re: 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 ***
Click to expand...
 
P

Paul Black

Brilliant Tom, Thanks Very Much.
Just One thing, I Included Option Explicit and Ran the Macro, I got an
ERROR on Line :-

Application.SumIf(rng3.Offset(­0, -1), ">" & l, rng3)

I Deleted the Minus Before the Zero and All Worked Great. Is there a
Specific Reason that the Minus Needs to be there Please.
Also, I Included Lbound & Ubound so that I don't have to Physically
Change the Values ( Only in the nType Array ).

Option Explicit
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 nType(1 To 203) As Long
Dim rng As Range
Dim s As Long, s1 As Long
Dim grpsize As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range
Dim i As Long, j As Long, k As Long
Dim l As Long

'
' set group size
'
grpsize = 20

Application.ScreenUpdating = False
Sheets("Results1").Select
Range("B3").Select
Columns("B:F").ClearContents

For i = LBound(nType) To UBound(nType)
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
nType(s1) = nType(s1) + 1
Next F
Next E
Next D
Next C
Next B
Next A

Set rng1 = ActiveCell.Offset(1, 2)
For i = LBound(nType) To UBound(nType)
ActiveCell.Offset(1, 0).Value = "Total for"
ActiveCell.Offset(1, 1).Value = i
ActiveCell.Offset(1, 2).NumberFormat = "#,##0"
ActiveCell.Offset(1, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i
Set rng2 = ActiveCell.Offset(0, 2)
ActiveCell.Offset(1, 0).Value = "Grand Total"
Set rng3 = Range(rng1, rng2)
Set rng = ActiveCell.Offset(1, 2)
ActiveCell.Offset(1, 2).NumberFormat = "#,##0"
rng.Value = Application.Sum(rng3)

ActiveCell.Offset(2, 0).Select
k = Application.RoundUp(rng3.Count / grpsize, 0)

j = LBound(nType)
Set rng4 = rng(2 + 1, 1)
Set rng5 = rng(2 + k, 1)
Set rng6 = Range(rng4, rng5)
For i = 1 To k
l = j + grpsize - 1
If l > rng2.Offset(0, -1).Value Then
l = rng2.Offset(0, -1).Value
End If
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = j & " to " & l
rng(2 + i, 1).Value = Application.SumIf( _
rng3.Offset(0, -1), ">=" & j, rng3) - _
Application.SumIf(rng3.Offset(0, -1), _
">" & l, rng3)
rng(2 + i, 1).NumberFormat = "#,##0"
j = l + 1
Next

rng5.Offset(2, -2).Value = "Grand Total"
rng5.Offset(2, 0).NumberFormat = "#,##0"
rng5.Offset(2, 0).Value = Application.Sum(rng6)

Application.ScreenUpdating = True
End Sub

Thanks Again.
All the Best.
Paul



Re: 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 nType(203) As Long
Dim nTypeTotal As Long
Dim rng As Range
Dim s As Long, s1 As Long
Dim grpsize As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range
Dim i As Long, j As Long, k As Long
Dim l As Long

'
' set group size
'
grpsize = 20

Application.ScreenUpdating = False
Sheets("Results").Select
Range("B3").Select
Columns("B:F").ClearContents

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
nType(s1) = nType(s1) + 1
Next F
Next E
Next D
Next C
Next B
Next A

nTypeTotal = ActiveCell.Row
Set rng1 = ActiveCell.Offset(1, 2)
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
Set rng2 = ActiveCell.Offset(0, 2)
ActiveCell.Offset(1, 0).Value = "Grand Total"
Set rng3 = Range(rng1, rng2)
Set rng = ActiveCell.Offset(1, 2)
rng.Value = Application.Sum(rng3)

ActiveCell.Offset(2, 0).Select
k = Application.RoundUp(rng3.Count / grpsize, 0)

j = 103
Set rng4 = rng(2 + 1, 1)
Set rng5 = rng(2 + k, 1)
Set rng6 = Range(rng4, rng5)
For i = 1 To k
l = j + grpsize - 1
If l > rng2.Offset(0, -1).Value Then
l = rng2.Offset(0, -1).Value
End If
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = j & " to " & l
rng(2 + i, 1).Value = Application.SumIf( _
rng3.Offset(0, -1), ">=" & j, rng3) - _
Application.SumIf(rng3.Offset(0, -1), _
">" & l, rng3)
rng(2 + i, 1).NumberFormat = "#,##0"
j = l + 1
Next

rng5.Offset(2, -2).Value = "Grand Total"
rng5.Offset(2, 0).NumberFormat = "#,##0"
rng5.Offset(2, 0).Value = Application.Sum(rng6)

Application.ScreenUpdating = True
End Sub
 
T

Tom Ogilvy

I didn't put a minus before a zero and the code ran fine for me. Event in
the code you quoted, I don't see a minus before a zero.
 
P

Paul Black

Hi Tom,

I have just Tried it Again. I know what Happened.
I Copied & Pasted ( into an Excel Sheet ) the Snippet of Code from your
Post …

Application.SumIf(rng3.Offset(­0, -1), > & l, rng3)

… and for some Reason it ADDED the Minus in Front of the Zero on the
Paste.
Also, I have Copied and Pasted this Snippet of Code Back into this Post,
Including the Minus. It is Showing the Minus in Front of the Zero, But I
am Sure when this Post Actually Shows up it will have Dropped the Minus.
I Don't know if Anyone Else has Ever Encountered this, But it Might be
Worth Keeping an Eye Out for.
Was my Use of LBound & UBound OK.

Thanks Again for ALL your Help.
All the best.
Paul



Re: Help with Totalling Groups Please
From: Tom Ogilvy

I didn't put a minus before a zero and the code ran fine for me. Event
in
the code you quoted, I don't see a minus before a zero.

--
Regards,
Tom Ogilvy



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

Paul Black

Hi Tom,

One Final Question Please.
I have Worked through how the Code is Structured and have Managed to see
the Logic Behind its Construction.
There is One Bit However that I Don't Fully Understand. What Does the
"rng(2 + i," Bit do Exactly. Does this Mean Offset 2 Columns and Keep a
Running Total of the Values for i. I Know that the "rng" is the Total of
ALL the Values from "rng1" to "rng2" ( in Other Words the Total of
"rng3" ).

For i = 1 To k
l = j + grpsize - 1
If l > rng2.Offset(0, -1).Value Then
l = rng2.Offset(0, -1).Value
End If
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = j & " to " & l
rng(2 + i, 1).Value = Application.SumIf( _
rng3.Offset(0, -1), ">=" & j, rng3) - _
Application.SumIf(rng3.Offset(0, -1), _
">" & l, rng3)
rng(2 + i, 1).NumberFormat = "#,##0"
j = l + 1
Next

Thanks in Advance.
All the Best.
Paul



Re: Help with Totalling Groups Please
Tom Ogilvy <[email protected]>

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 nType(203) As Long
Dim nTypeTotal As Long
Dim rng As Range
Dim s As Long, s1 As Long
Dim grpsize As Long
Dim rng1 As Range, rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range
Dim i As Long, j As Long, k As Long
Dim l As Long

'
' set group size
'
grpsize = 20

Application.ScreenUpdating = False
Sheets("Results1").Select
Range("B3").Select
Columns("B:F").ClearContents

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
nType(s1) = nType(s1) + 1
Next F
Next E
Next D
Next C
Next B
Next A

nTypeTotal = ActiveCell.Row
Set rng1 = ActiveCell.Offset(1, 2)
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
Set rng2 = ActiveCell.Offset(0, 2)
ActiveCell.Offset(1, 0).Value = "Grand Total"
Set rng3 = Range(rng1, rng2)
Set rng = ActiveCell.Offset(1, 2)
rng.Value = Application.Sum(rng3)

ActiveCell.Offset(2, 0).Select
k = Application.RoundUp(rng3.Count / grpsize, 0)

j = 103
Set rng4 = rng(2 + 1, 1)
Set rng5 = rng(2 + k, 1)
Set rng6 = Range(rng4, rng5)
For i = 1 To k
l = j + grpsize - 1
If l > rng2.Offset(0, -1).Value Then
l = rng2.Offset(0, -1).Value
End If
rng(2 + i, -1).Value = "Total for"
rng(2 + i, 0).Value = j & " to " & l
rng(2 + i, 1).Value = Application.SumIf( _
rng3.Offset(0, -1), ">=" & j, rng3) - _
Application.SumIf(rng3.Offset(0, -1), _
">" & l, rng3)
rng(2 + i, 1).NumberFormat = "#,##0"
j = l + 1
Next

rng5.Offset(2, -2).Value = "Grand Total"
rng5.Offset(2, 0).NumberFormat = "#,##0"
rng5.Offset(2, 0).Value = Application.Sum(rng6)

Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy




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

Tom Ogilvy

rng is a reference to a cell.

It is set in this line:

Set rng = ActiveCell.Offset(1, 2)


this is where your first grand total is placed, but what is in the cell is
of no significance to the rest of the code. rng is used as a referenance
from which to base the location of other information.

rng(2 + i,1)

says to go down two rows below the cell refered to by rng.

so if rng was A1

set rng = Range("A1")
i = 1
? rng(2 + i,1).Address
$A$3

A1 is rng(1,1)
A2 is rng(2,1)
A3 is rng(3,1)

B3 is rng(3,2)

so it is similar to offset, but 1 based, where offset is zero based.

rng(1,1) is the same as rng.offset(0,0)
 
P

Paul Black

Thanks Tom,

That has Made it Clearer.
So Basically, if rng was say D5 :-
rng(1,1) would be D5
rng(1,0) would be C5 &
rng(1,-1) would be B5.
I am Still Unclear though what the "+ i" in rng(2 + i, 1) & the "+ 1" in
rng(2 + 1, 1) Actually Does.
Also, what Does the ? Do Please.

Thanks in Advance.
All the Best.
Paul



Re: Help with Totalling Groups Please
From: Tom Ogilvy

rng is a reference to a cell.

It is set in this line:

Set rng = ActiveCell.Offset(1, 2)


this is where your first grand total is placed, but what is in the cell
is
of no significance to the rest of the code. rng is used as a referenance
from which to base the location of other information.

rng(2 + i,1)

says to go down two rows below the cell refered to by rng.

so if rng was A1

set rng = Range("A1")
i = 1
? rng(2 + i,1).Address
$A$3

A1 is rng(1,1)
A2 is rng(2,1)
A3 is rng(3,1)

B3 is rng(3,2)

so it is similar to offset, but 1 based, where offset is zero based.

rng(1,1) is the same as rng.offset(0,0)

--
Regards,
Tom Ogilvy



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

Paul Black

Thanks Tom,

That has Made it Clearer.
So Basically, if rng was say D5 :-
rng(1,1) would be D5
rng(1,0) would be C5 &
rng(1,-1) would be B5.
I am Still Unclear though what the "+ i" in rng(2 + i, 1) & the "+ 1" in
rng(2 + 1, 1) Actually Does.
Also, what Does the ? Do Please.

Thanks in Advance.
All the Best.
Paul

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
AddPicturesToShapes 0
Can this Code be Condensed 23
Total a Variable Based on Another Variable Please 0
Working outlook via vba code with xlsx - error 0
Modify working code to expand the output. 5
Help With Total of Values Please 8

Top