Sub PlaceSumFormula()
Dim rng As Range
Dim n As Long
For Each rng In Range("I:N").SpecialCells(xlCellTypeFormulas, 1).Areas
n = rng.Rows.Count
If n = 6 Or n = 8 Then
rng.Cells(rng.Rows.Count).Select
rng.Cells(rng.Rows.Count).Formula = "=SUM(R[-" & rng.Rows.Count - 1 & "]C:R[-1]C)"
End If
Next rng
End Sub
Sub test1()
' author ProfessorJimJam Sep 2016
Dim tbl As ListObject
For Each sh In ActiveWorkbook.Sheets
For Each tbl In sh.ListObjects
tbl.ListColumns.Add
x = tbl.ListColumns.Count
tbname = tbl.Name & "[[#All]"
tbcolname = "[" & tbl.ListColumns(1).Name & "]]"
tbl.Range(2, x).FormulaR1C1 = "=SUMPRODUCT(SMALL(" & tbname & "," & tbcolname & ",ROW(INDIRECT(""1:2""))))"
tbl.ListColumns.Add
x = tbl.ListColumns.Count
tbl.Range(2, x).FormulaR1C1 = "=SUMPRODUCT(LARGE(" & tbname & "," & tbcolname & ",ROW(INDIRECT(""1:2""))))"
Next tbl
Next sh
End Sub
ExpertExchangeTopBottomExample.xlsm
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Need to Obtain or Remove Password Protection From an Excel 2000 Workbook | 6 | 57 | |
Excel calculate based on 'x' in column | 2 | 25 | |
Copying from excel I am getting extra text | 11 | 44 | |
Embeded Images | 13 | 13 |
Join the community of 500,000 technology professionals and ask your questions.