Subtotal \ Grand Total in VBA

I need to subtotal a range at each change in "CTR" with VBA code.
I acheived this with a macro recording but I can't figure out the next part which is this.

I need to get the Grand total that appears at the bottom of the sum range
To show up at the top of the worksheet in range ("P2").


I tried to make the cell equal xlSUM but that always gives -4175


Thanks
Charles

It sounds like you are using the built in subtotal method. Assume column P
contains the values you want to grandtotal.

After your code that builds the subtotal, try putting in lines of code like
this

set rng = Range("P3",cells(rows.count,"P").End(xlup))
Range("P2").formula = "=subtotal(9," & rng.Address & ")"

Adjust to fit your actual locations.

--
Regards,
Tom Ogilvy


"mrdata" wrote:

>
> I need to subtotal a range at each change in "CTR" with VBA code.
> I acheived this with a macro recording but I can't figure out the next
> part which is this.
>
> I need to get the Grand total that appears at the bottom of the sum
> range
> To show up at the top of the worksheet in range ("P2").
>
>
> I tried to make the cell equal xlSUM but that always gives -4175
>
>
> Thanks
> Charles
>
>
> --
> mrdata
> ------------------------------------------------------------------------
> mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
> View this thread: http://www.excelforum.com/showthread...hreadid=543677
>
>

Tom:
The code works great but it does not give me the result's I need.

First I only want to have a grand total in range P2
Currently I get the grand total below the data and in range P2

Also Is there a way to format the subtotal and grand total results to show thousands. without having to do it manualy.

Here is the code I am using.


Range("A4").Select
Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(9), _
Replace:=True ', PageBreaks:=False ', SummaryBelowdata:=True

Set rng = Range("I5", Cells(Rows.Count, "I").End(xlUp))
Range("P2").Formula = "=subtotal(9," & rng.Address & ")"

I have the Summarybelowdata part remed out but it still gives a Grand total
Below data.


Thanks
Charles