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
|

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

Thanks Dave, that explains a LOT!

--
|
+-- Thief_
|

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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