Continue to have issue with array in Subtotals automation



Hi all,

I had posted on this issue previously, but was not able to find a solution
from those who provided input. Below is a better description of what I'm
trying to do and the issue I'm having:

I am trying to get the code to subtotal beginning with column BA and going
through the last column to the right (the number of columns to the right of
BA can vary). The subtotal needs to be based on changes in column A (ie -
subtotal for "Bob", then for "Mary", etc) -- the headers are in row 2 and the
data to be summed begins with row 3 (after a blank row 3 is deleted by one of
the first lines in the code). The line of code below noted by ** results in
the following error:

Run-time error '1004': Subtotal method of Range class failed

Sub subtotalcum()

Dim aryCols() As Variant
Dim i As Integer
Dim max As Integer

Dim rng As Range

With Sheets("Commission by Entity breakdown")

.Rows("3:3").Delete shift:=xlUp

On Error Resume Next
Set rng = .Range(.Range("BA2"),
..Range("IV2").End(xlToLeft).Offset(0, -8))
max = rng.Count
ReDim aryCols(1 To max)
On Error GoTo 0
If Not rng Is Nothing Then

For i = 1 To max
aryCols(i) = i + 52
Next i

.Range("A2").subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=aryCols(), _
Replace:=True, _
PageBreaks:=False, _
End If
End With

End Sub



Dave Peterson

When I had good data in my worksheet, your code worked fine for me.

But if I screwed up and didn't have nice data, then I could get the error.

I'd remove the "on error resume Next" line (along with the "on error goto 0"
line) and verify that my rng was what I wanted:

Msgbox rng.address

And even step through the code to see what was in arrcols. Maybe it wasn't what
you wanted.




Hi Dave,

Thanks for the suggestion. I found that the problem was that there weren't
values in some cells in the header row -- once I put values into those cells,
the code worked perfectly.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtotal Formatting 2
Subtotal by VBA 5
Subtotal for same range of multiple worksheets 5
Issue with nested data subtotals 3
Excel Subtotals 2
Subtotal function 2
Code for Subtotals 9
Rounding Subtotals to 2 places 1