PDA

View Full Version : Autosum current column in a table



account123
12-01-2016, 12:28 PM
Hi...

My current activecell is one of the cells in the Totals row in a table, i have so many tables with different row count so the "FormulaR1C1" method is not working for me cause each time the values are different, i am looking for a code to enable me to autosum the whole column of the table.

Each table is in a separate workbook and all of them are named "Table1".

I Can manage to select the right cell in each table with my current code, but i cant autosum this column.

what i need is something like this, for example:

1. application.activecell.sum(thiscolumn)!

2. activecell=sum(thiscolumn)

On the other hand i have 2 cells (BQ6 & BQ7) which have the right First and Last cell to sum as text i.e" cell BQ6 has the text "E3" and cell BQ7 has the text "E8", and the right cell range i need to sum is actually E3:E8, so i tried this code and it didn't work:
Dim i as Range
Dim b as Range
Set i = Range(Range("BQ5").Value)
Set b = Range(Range("BQ6").Value)
ActiveCell.FormulaR1C1="=Sum(i:b)"

Thank you so much...

Bob Phillips
12-01-2016, 03:16 PM
If it's a table, why don't you just add a Table Totals row, let Excel manage it for you.

account123
12-01-2016, 03:19 PM
I actually added a Totals Row but how can i tell excel which Column to Sum?

Bob Phillips
12-01-2016, 03:24 PM
You can go into each Totals cell, and click the dropdown arrow at the side. There is a None option.

account123
12-01-2016, 03:34 PM
I HAVE HUNDREDS OF TABLES AND AFTER THE CODE RUNS IN EACH ONE OF THEM IT WILL PRINT THE TABLE OUT SO I NEED ONLY TO SELECT SPECIFIC COLUMNS TO SUM, ITS A BUSINESS REPORT AND I CANT HAVE ERRORS AND SUMS FOR UNNECESSARY DATA LIKE A COLUMN FOR DATE OR SERIAL NUMBER.

Bob Phillips
12-02-2016, 11:38 AM
WELL SHOUTING AT ME ISN'T GOING TO HELP ANYTHING!

Aussiebear
12-03-2016, 02:21 AM
Hopefully that was only an accident...