Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Automate Subtotals Insertion?
I'm trying to automate a Subtotal function, ie, I want to SUM the data
in columns C onwards for each change in column A: Code: Private Sub SubTotalSummaryData(LasDateCol As Byte) Dim DataArray() As Byte, cnt As Byte, i As Byte With Worksheets("Summary") ' Populate array of all used columns of data- used by the Subtotal function. ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _ :=True End With End Sub The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it. Any ideas? -- | +-- Julian | |
#2
|
|||
|
|||
That array doesn't have to be 0 based or 1 based. You can actually just use the
columns you want: Option Explicit Sub testme() With Worksheets("Summary") Call SubTotalSummaryData(.Cells(1, .Columns.Count).End(xlToLeft).Column) End With End Sub Private Sub SubTotalSummaryData(LasDateCol As Long) Dim DataArray() As Long Dim i As Long With Worksheets("Summary") ' Populate array of all used columns of data- used by ' the Subtotal function. ReDim DataArray(3 To LasDateCol) For i = 3 To LasDateCol DataArray(i) = i Next i .UsedRange.Subtotal GroupBy:=1, _ Function:=xlSum, TotalList:=Array(DataArray()), _ Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True End With End Sub I changed the Byte's to Long's. Thief_ wrote: I'm trying to automate a Subtotal function, ie, I want to SUM the data in columns C onwards for each change in column A: Code: Private Sub SubTotalSummaryData(LasDateCol As Byte) Dim DataArray() As Byte, cnt As Byte, i As Byte With Worksheets("Summary") ' Populate array of all used columns of data- used by the Subtotal function. ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _ :=True End With End Sub The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it. Any ideas? -- | +-- Julian | -- Dave Peterson |
#3
|
|||
|
|||
Wonderful- it works, but I can't understand why my original code doesn't:
ReDim DataArray(LasDateCol) cnt=0 For i = 3 To LasDateCol DataArray(cnt) = i cnt=cnt+1 Next i .....which is a zero-based array?? -- | +-- Julian | "Dave Peterson" wrote in message ... That array doesn't have to be 0 based or 1 based. You can actually just use the columns you want: Option Explicit Sub testme() With Worksheets("Summary") Call SubTotalSummaryData(.Cells(1, ..Columns.Count).End(xlToLeft).Column) End With End Sub Private Sub SubTotalSummaryData(LasDateCol As Long) Dim DataArray() As Long Dim i As Long With Worksheets("Summary") ' Populate array of all used columns of data- used by ' the Subtotal function. ReDim DataArray(3 To LasDateCol) For i = 3 To LasDateCol DataArray(i) = i Next i .UsedRange.Subtotal GroupBy:=1, _ Function:=xlSum, TotalList:=Array(DataArray()), _ Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True End With End Sub I changed the Byte's to Long's. Thief_ wrote: I'm trying to automate a Subtotal function, ie, I want to SUM the data in columns C onwards for each change in column A: Code: Private Sub SubTotalSummaryData(LasDateCol As Byte) Dim DataArray() As Byte, cnt As Byte, i As Byte With Worksheets("Summary") ' Populate array of all used columns of data- used by the Subtotal function. ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _ :=True End With End Sub The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it. Any ideas? -- | +-- Julian | -- Dave Peterson |
#4
|
|||
|
|||
But your original code was:
ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i And unless you you have "option base 1" at the top of your module, it's a 0 based array. Say you wanted columns 3 to 5 (5 being the last column) for a total of 3 columns redim dataarray(5-2) redim dataarray(3) gives an array with 4 elements: dataarray(0) dataarray(1) dataarray(2) dataarray(3) And you don't populate dataarray(3). So one fix would have been: Redim DataArray(lasdatecol -3) or redim dataarray(1 to lasdatecol -2) cnt = 1 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i ========== I just thought it would be easier to see the loop without the extra cnt variable. In fact, to keep from having to update those bounds, I'd probably use something more like: ReDim DataArray(3 To LasDateCol) For i = LBound(DataArray) To UBound(DataArray) DataArray(i) = i Next i If I changed that 3, I'd only have to change it in one spot. Thief_ wrote: Wonderful- it works, but I can't understand why my original code doesn't: ReDim DataArray(LasDateCol) cnt=0 For i = 3 To LasDateCol DataArray(cnt) = i cnt=cnt+1 Next i ....which is a zero-based array?? -- | +-- Julian | "Dave Peterson" wrote in message ... That array doesn't have to be 0 based or 1 based. You can actually just use the columns you want: Option Explicit Sub testme() With Worksheets("Summary") Call SubTotalSummaryData(.Cells(1, .Columns.Count).End(xlToLeft).Column) End With End Sub Private Sub SubTotalSummaryData(LasDateCol As Long) Dim DataArray() As Long Dim i As Long With Worksheets("Summary") ' Populate array of all used columns of data- used by ' the Subtotal function. ReDim DataArray(3 To LasDateCol) For i = 3 To LasDateCol DataArray(i) = i Next i .UsedRange.Subtotal GroupBy:=1, _ Function:=xlSum, TotalList:=Array(DataArray()), _ Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True End With End Sub I changed the Byte's to Long's. Thief_ wrote: I'm trying to automate a Subtotal function, ie, I want to SUM the data in columns C onwards for each change in column A: Code: Private Sub SubTotalSummaryData(LasDateCol As Byte) Dim DataArray() As Byte, cnt As Byte, i As Byte With Worksheets("Summary") ' Populate array of all used columns of data- used by the Subtotal function. ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _ :=True End With End Sub The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it. Any ideas? -- | +-- Julian | -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Thanks Dave, that explains a LOT!
-- | +-- Thief_ | "Dave Peterson" wrote in message ... But your original code was: ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i And unless you you have "option base 1" at the top of your module, it's a 0 based array. Say you wanted columns 3 to 5 (5 being the last column) for a total of 3 columns redim dataarray(5-2) redim dataarray(3) gives an array with 4 elements: dataarray(0) dataarray(1) dataarray(2) dataarray(3) And you don't populate dataarray(3). So one fix would have been: Redim DataArray(lasdatecol -3) or redim dataarray(1 to lasdatecol -2) cnt = 1 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i ========== I just thought it would be easier to see the loop without the extra cnt variable. In fact, to keep from having to update those bounds, I'd probably use something more like: ReDim DataArray(3 To LasDateCol) For i = LBound(DataArray) To UBound(DataArray) DataArray(i) = i Next i If I changed that 3, I'd only have to change it in one spot. Thief_ wrote: Wonderful- it works, but I can't understand why my original code doesn't: ReDim DataArray(LasDateCol) cnt=0 For i = 3 To LasDateCol DataArray(cnt) = i cnt=cnt+1 Next i ....which is a zero-based array?? -- | +-- Julian | "Dave Peterson" wrote in message ... That array doesn't have to be 0 based or 1 based. You can actually just use the columns you want: Option Explicit Sub testme() With Worksheets("Summary") Call SubTotalSummaryData(.Cells(1, .Columns.Count).End(xlToLeft).Column) End With End Sub Private Sub SubTotalSummaryData(LasDateCol As Long) Dim DataArray() As Long Dim i As Long With Worksheets("Summary") ' Populate array of all used columns of data- used by ' the Subtotal function. ReDim DataArray(3 To LasDateCol) For i = 3 To LasDateCol DataArray(i) = i Next i .UsedRange.Subtotal GroupBy:=1, _ Function:=xlSum, TotalList:=Array(DataArray()), _ Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True End With End Sub I changed the Byte's to Long's. Thief_ wrote: I'm trying to automate a Subtotal function, ie, I want to SUM the data in columns C onwards for each change in column A: Code: Private Sub SubTotalSummaryData(LasDateCol As Byte) Dim DataArray() As Byte, cnt As Byte, i As Byte With Worksheets("Summary") ' Populate array of all used columns of data- used by the Subtotal function. ReDim DataArray(LasDateCol - 2) cnt = 0 For i = 3 To LasDateCol DataArray(cnt) = i cnt = cnt + 1 Next i .UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False, SummaryBelowData _ :=True End With End Sub The number of columns present will vary each time the procedure is called thus I tried creating an array of all the used columns via the FOR....NEXT loop, but VBA doesn't like it. Any ideas? -- | +-- Julian | -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel gets subtotals out of order using multiple sorts and subtot. | Excel Discussion (Misc queries) | |||
Subtotals | Excel Discussion (Misc queries) | |||
Subtotals Problem when Filtering | Excel Discussion (Misc queries) | |||
In Excel, how do you copy and paste just the subtotals into anoth. | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |