Continue to have issue with array in Subtotals automation

[robs3131]

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:

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, _
SummaryBelowData:=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.

 

[robs3131]

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.

Thanks!

Robert