Autosum table column

account123

New Member
Joined
Oct 1, 2016
Messages
11
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...
 

account123

New Member
Joined
Oct 1, 2016
Messages
11
thats looks good but the FieldName is different in every table, i can insert the correct field name in cell BQ8 but then i need o declare it at somethimg? i guess
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
The Field name will always be the header name from the table.
Like tables would be best using like headers simplifying those relationships.
 

account123

New Member
Joined
Oct 1, 2016
Messages
11
ok what would be the proper Dim method here or can u give me an example with a code please
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,694
thats looks good but the FieldName is different in every table, i can insert the correct field name in cell BQ8 but then i need o declare it at somethimg? i guess
Code:
BQRef= Range("BQ8").value
 ActiveCell.FormulaR1C1="=Sum(TableName,["&BQRef&"])"
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top