PDA

View Full Version : [SOLVED] Setting .SubTotal TotalList via VBA



MWE
04-03-2005, 06:02 PM
When using the SubTotal Method,


Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4, 5) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
one of the arguements is TotalList. Per VBA Help:

TotalList Required Variant. An array of 1-based field offsets, indicating the fields to which the subtotals are added.

I have tried to define or set TotalList several different ways, but other than something similar to code snipet above, nothing has worked. I would have thought that a simple variant array would work, but it does not.

How can I programatically set TotalList?

Thanks

mvidas
04-04-2005, 09:32 AM
MWE,

It looks like you want a macro that will always perform the subtotal on the selection, using the first selected column as the "group by", and each additional column as the TotalList?
If you wanted 2, 3, 4, 5 to be the TotalList, and "arr" was your variant array, you would need to assign it like:

arr(0) = 2
arr(1) = 3
arr(2) = 4
arr(3) = 5
But of course, that is knowing what fields you need. You can do it programatically like:


Sub subt()
Dim TotalListArr(), i As Long, j As Long
ReDim TotalListArr(Selection.Columns.Count - 2)
For i = 2 To Selection.Columns.Count
TotalListArr(j) = i
j = j + 1
Next i
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotalListArr, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=xlSummaryBelow
End Sub

Any questions, let me know!
Matt

MWE
04-04-2005, 06:01 PM
MWE,

It looks like you want a macro that will always perform the subtotal on the selection, using the first selected column as the "group by", and each additional column as the TotalList?
If you wanted 2, 3, 4, 5 to be the TotalList, and "arr" was your variant array, you would need to assign it like:

arr(0) = 2
arr(1) = 3
arr(2) = 4
arr(3) = 5
But of course, that is knowing what fields you need. You can do it programatically like:


Sub subt()
Dim TotalListArr(), i As Long, j As Long
ReDim TotalListArr(Selection.Columns.Count - 2)
For i = 2 To Selection.Columns.Count
TotalListArr(j) = i
j = j + 1
Next i
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=TotalListArr, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=xlSummaryBelow
End Sub

Any questions, let me know!
Matt

The only difference between what you suggest and what I tried was that I dimmed and typed the TotalList array, i.e.,


Dim varTotalList(5) as variant

My loop to stuff values in the array is virtually identical. Execution halted with an error statement indicating that TotalList:=varTotalList was unacceptable. I will try the non-typed approach.