Excel - 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 =
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.

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.


Formula = "=SUM(" & SumAddr & ")"

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 & ")"

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