subtotals
Posted by brad davis on February 13, 2002 2:06 PM
I'm trying to dynamically create the Array(1,2,...) for the TotalList arg of the Subtotal method such that:
ListOfValues = "1,2,3"
Selection.Subtotal 1, xlSum, Array(ListOfValues), True, False, True
but it fails when I run it. Error 1004 "Subtotal method of Range class failed" error
Help please
If I hard code the TotalList arg as
TotalList:=Array(1,2,3) it works.
Anyone know a workaround?
Posted by Juan Pablo G. on February 13, 2002 4:32 PM
If you're using Excel 2000 or later you could use
ListOfValues = "1,2,3"
ArrayListOfValues = Split(ListOfValues,",")
Juan Pablo G.
Posted by Russell Hauf on February 13, 2002 4:40 PM
Sure. Declare an array and fill it's values. But you will also need to dynamically size the array, unless it's always going to have the same number of values. For example:
Dim aintList() as Integer
Dim intCount as Integer
intCount = 1
' ... your code
' now you want to define the values
aintList(1 to intCount) = MyValue
intCount = intCount + 1
' if you want to add another value:
ReDim Preserve aintList(1 to intCount)
aintList(intCount) = MyNextValue
intCount = intCount + 1
' etc. - you could also do this in a loop...
Selection.Subtotal, yada, yada, TotalList:=aintList
================
Get the picture? If you are always going to have 3 values in your TotalList array, you can just define your array like this:
Dim aintList(1 to 3) as Integer
and then assign values to the 1,2, and 3 positions in the array.
Hope this helps,
Russell
