Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
add sum with macro to list
Hi,
How can I add a SUM function via a macro underneath a list which can differ in length. I know how to get to the bottom of the list using CTRL-DOWN in "relative mode" but then I get into trouble with the range in the formula. Any help on this is welcome. Cheers, Harold |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
add sum with macro to list
Try this will sum A2 to last row
With Worksheets("Sheet1") If IsEmpty(.Cells(.Rows.Count, 1)) Then With .Cells(.Rows.Count, 1).End(xlUp) .Offset(2, 0).Formula = "=Sum($A$2:" & _ .Address & ")" End With End If End With "mohavv" wrote: Hi, How can I add a SUM function via a macro underneath a list which can differ in length. I know how to get to the bottom of the list using CTRL-DOWN in "relative mode" but then I get into trouble with the range in the formula. Any help on this is welcome. Cheers, Harold |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
add sum with macro to list
This uses column A to find the next row:
Option Explicit Sub testme() Dim NextRow As Long With ActiveSheet 'use column A to determine the NextRow NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(NextRow, "A").FormulaR1C1 = "=sum(r1c:r[-1]c)" End With End Sub If you wanted to fill in (say) columns B:F with the similar formula (avoiding column A): Option Explicit Sub testme() Dim NextRow As Long With ActiveSheet 'use column A to determine the NextRow NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(NextRow, "B").Resize(1, 5).FormulaR1C1 = "=sum(r1c:r[-1]c)" End With End Sub A note about the .FormulaR1C1 reference style: =sum(r1c:r[-1]c) r1c is the first row in the same column as the cell with the formula (Row 1, same column) r[-1]c is one cell up from the cell with the formula in the same column. If you wanted Row 2 through one cell up, you'd use: ..FormulaR1C1 = "=sum(r2c:r[-1]c)" mohavv wrote: Hi, How can I add a SUM function via a macro underneath a list which can differ in length. I know how to get to the bottom of the list using CTRL-DOWN in "relative mode" but then I get into trouble with the range in the formula. Any help on this is welcome. Cheers, Harold -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort a list macro. | Excel Discussion (Misc queries) | |||
Macro for list | Excel Discussion (Misc queries) | |||
delete a macro that isn't in macro list | Excel Discussion (Misc queries) | |||
List Box with macro | Excel Worksheet Functions | |||
How to create a macro that compares a list to another list | New Users to Excel |