PDA

View Full Version : Inset formula into last cell of table column



Si2873
07-10-2019, 02:05 AM
I have a table called Table1 on Sheet1 of an Excel workbook which can contain any number of rows. I want to add a row below the last row of the table, which I am doing with:


Sub insertRow()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
tbl.ListRows.Add

I now want to add SUM functions to the new last cell of 3 Table1 columns (Quantity, Price, Cost).
I would appreciate it if someone could give me some guidance on how to achieve this.

Finally, I would like to reference these SUM values from Sheet2 e.g. place the Sum of Table1 column Cost into Cell A1 on Sheet2.
Again, any help with this would be greatly appreciated.

TIA

大灰狼1976
07-12-2019, 02:11 AM
Hi Si2873!
Welcome to vbax forum.
If you can upload an attachment, it is estimated that the problem will be solved soon.

Si2873
07-12-2019, 03:41 AM
Hi Si2873!
Welcome to vbax forum.
If you can upload an attachment, it is estimated that the problem will be solved soon.


Hi,

Thank you for taking the time to reply.

24578

In the attached example Sheet1 and Sheet2 are what I am starting with and Sheet3 and Sheet4 are examples of which I want Sheet1 and Sheet2 to look like at the end.

Any help or guidance would be greatly appreciated.

Artik
07-12-2019, 04:13 PM
Sub Makro4()

With Worksheets("Sheet1").ListObjects(1)
.ShowTotals = True
.ListColumns("Quantity").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Price").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Cost").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("Date").TotalsCalculation = xlTotalsCalculationNone
End With


With Worksheets("Sheet2").ListObjects(1).ListRows(1).Range
.Cells(1).FormulaR1C1 = "=Table1[[#Totals],[Quantity]]"
.Cells(2).FormulaR1C1 = "=Table1[[#Totals],[Price]]"
.Cells(3).FormulaR1C1 = "=Table1[[#Totals],[Cost]]"
End With
End Sub
Artik