ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Automate Subtotals Insertion? (https://www.excelbanter.com/excel-worksheet-functions/22121-automate-subtotals-insertion.html)

Thief_
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
|

Dave Peterson
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

Thief_
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
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

Thief_
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


All times are GMT +1. The time now is 06:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com