Method of object failed

[boblarson]

I am trying to programmatically get subtotals in my Excel worksheet (after sending the data from Access and still using an Access module to do this) and I keep getting an error

It works fine if I use specific numbers:

.Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 8, 9, 10, 11), _
                            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

But fails if I try to use a variable (which has the same exact values of 7, 8, 9, 10, 11)

        .Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(strSubTotalList), _
                            Replace:=True, PageBreaks:=False, SummaryBelowData:=True

There must be something in the way I'm trying to use the variable in the array. Any ideas?

 

[chergh]

I'm not sure you can use a string variable like that to pass the values. You need to create an array variable instead and then pass that to the subtotal method:

dim strSubTotalList(1 to 5)
dim i as long
dim j as long

j = 7

for i = 1 to 5

strSubTotalList(i) = j

j =j + 1

next i

and then

.Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=strSubTotalList, _
                            Replace:=True, PageBreaks:=False, SummaryBelowData:=True