Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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" wrote in message ... 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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 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 -- Regards, Tom Ogilvy *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 "Paul Black" wrote in message ... 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 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 -- Regards, Tom Ogilvy *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 Help with Totalling Groups Please 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("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 *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 "Paul Black" wrote in message ... 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 Help with Totalling Groups Please 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("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 *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 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 *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totalling Groups Please
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Sorting groups in groups due to subtotaling | Excel Worksheet Functions | |||
My SUM function is totalling to 0 | Excel Worksheet Functions | |||
Totalling volumes per box | Excel Worksheet Functions | |||
totalling | Excel Discussion (Misc queries) | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) |