Getting a Grand Total in Grand Total field only
 

Hello I have a spreadsheet, in which I have 3 tables in coloum I:N. Rows of these tables varies depending upon the data in the table. I need a macro which auto sums in the grand total row (Last Row of Each Table) for the data in the respective table.
The code that I am using is giving me an auto sum in the next balnk cell, but I want it to be in Grant Total row only. There may be gaps in the table.

As of now I am using the following code:

Sub Slide07_Global_AutoSum()

For Each NumRange In Columns("I:N").SpecialCells(xlConstants, xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange

NoData:
Call Slide06_SEMEA

End Sub

Getting a Grand Total in Grand Total field only
 

On Wednesday, June 27, 2012 2:23:58 AM UTC-5, Gaura215 wrote:
Hello I have a spreadsheet, in which I have 3 tables in coloum I:N. Rows
of these tables varies depending upon the data in the table. I need a
macro which auto sums in the grand total row (Last Row of Each Table)
for the data in the respective table.
The code that I am using is giving me an auto sum in the next balnk
cell, but I want it to be in Grant Total row only. There may be gaps in
the table.

As of now I am using the following code:

-*Sub Slide07_Global_AutoSum()

For Each NumRange In Columns("I:N").SpecialCells(xlConstants,
xlNumbers).Areas
SumAddr = NumRange.Address(False, False)
NumRange.Offset(NumRange.Count, 0).Resize(1, 1).Formula =
"=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange

NoData:
Call Slide06_SEMEA

End Sub*-




--
Gaura215

Why not just put the totals at the TOP. However, are you saying you want totals for all columns to be on the SAME row. if so, just identify that row and have the macro put in the formula there.

If desired, send ME a file.

Getting a Grand Total in Grand Total field only
 

"Gaura215" wrote:
I have a spreadsheet, in which I have 3 tables in
coloum I:N. Rows of these tables varies depending upon
the data in the table. I need a macro which auto sums
in the grand total row (Last Row of Each Table) for
the data in the respective table. [....] There may
be gaps in the table.

If there "may be gaps" in each table,
Columns("I:N").SpecialCells(xlConstants,xlNumbers) .Areas is not the right
thing for you to use at all. You need a radically different way to
determine the limits of each table.

You have not provided sufficient information for us to help you with that.
Upload an example Excel file to a file-sharing website, and post the URL of
the uploaded file here.

The following applies if there are __no__ gaps.


"Gaura215" wrote:
For Each NumRange In Columns("I:N"). _
SpecialCells(xlConstants,xlNumbers).Areas
SumAddr = NumRange.Address(False,False)
NumRange.Offset(NumRange.Count, 0).Resize(1, 1). _
Formula = "=SUM(" & SumAddr & ")"
c = NumRange.Count
Next NumRange

The problem is: NumRange.Count returns the total number of cells, not just
the number of rows. Perhaps the following puts the SUM formula where you
want it:

Dim r as Long, c as Long
r = NumRange.Rows.Count
c = NumRange.Columns.Count
NumRange.Offset(r,c).Resize(1,1).Formula = "=SUM" & SumAddr & ")"

That puts the SUM formula in the cell just below and to the right of the
table. Adjust c for the column that you actually want.

But you do not need c as I defined it if you want to put the SUM formula in
column I under the last row of numbers. The following would suffice:

Dim r as Long
r = NumRange.Rows.Count
NumRange.Offset(r,0).Resize(1,1).Formula = "=SUM" & SumAddr & ")"

Getting a Grand Total in Grand Total field only
 

The simplest way, IMO, would be to define a fully relative range with
local (sheet-level) scope, and use that in all formulas for totalling
all tables.

Example for a sheet named "Sheet1":
Select A2 and open the Define Name dialog;
In the NameBox type: 'Sheet1'!LastCell
In the RefersTo box type: =A1
Click 'Add' and close the dialog

Now you can have dynamic totals that will adjust when you insert/delete
rows. Layout your sheet as follows:

Row5 Table1 (heading)
Row6 Table1 data
Row7 Table1 data
Row8 Table1 data
Row9 Table1 Totals

If your amounts are in "I:N" then in cell "I9" enter this formula...

=SUM(I$5:LastCell)

...where the ref to column "I" is relative, and the ref to row '5' is
absolute. Copy this formula across to column "N" for this row of
totals.

Row10 Table1 (heading)
Row11 Table1 data
Row12 Table1 data
Row13 Table1 data
Row14 Table1 Totals

In cell "I14" enter this formula...

=SUM(I$10:LastCell)

...and copy across to "N14" on this row.

Repeat for each table as needed, adjusting the ref to the absolute row
as required. *Note* that the table heading row must be blank (or not
contain numeric values). Using this method will make your table totals
dynamically adjust when you need to add more rows or delete rows.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion