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
--
Robert

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 wrote:
>
> 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
> --
> Robert

--

Dave Peterson

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
--
Robert


"Dave Peterson" wrote:

> 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 wrote:
> >
> > 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
> > --
> > Robert
>
> --
>
> Dave Peterson
>