Summing a variable-length column

Question

Hi all.  I am trying to write a piece of code that will sum a column in excel.  The issue here is that column length that I am summing will change dynamically.  The code that I have written (and which doesnt work) is as follows:

ActiveCell.Formula = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(1, 0), Selection.End(xlDown)))

the rangeI am going to want to sum will always be: starting at the active cell offset by 1 row and ending at the end of the selection.  Does this make sense?  Also, ideally I would like for there to be a 'cell link' so that in excel, I can see that this particular cell equals the sum of the column.  I am not sure if it is possible to add something like that in there, i know the code would start with: ActiveCell.Formula = "= .... "

Any and all help would be much appreciated.  thanks!

Answer 1

Try

ActiveCell.FormulaR1C1 = "=SUM(R[1]C:" & ActiveCell.End(xlDown).Address(RowAbsolute:=False, _

    ColumnAbsolute:=False, ReferenceStyle:=xlR1C1, RelativeTo:=ActiveCell) & ")"

I don't know what you mean by "cell link"...

Answer 2

Thank you for all of the help guys.  What I meant by "link" was that in the actual spreadsheet, the cell was not hardcoded but rather showed in the formula bar that it was a summation of the column.  All of your suggestions seemed to capture this.

once again many many thanks!

Answer 3

Assuming your selection is one column wide, give this a try...

ActiveCell.Formula = WorksheetFunction.Sum(Range(ActiveCell, Selection(Selection.Count)))

I don't know why you are assigning this sum to the Formula property rather than the Value property, but it will produce the same end result. I am not sure what you mean by "cell link"... can you describe what you are looking for in more detail (perhaps with a example)?

If, on the other hand, you are looking to assign the actual formula to the ActiveCell that will do the summation, then you can use this line of code...

ActiveCell.Formula = "=SUM(" & ActiveCell.Offset(1).Address(0, 0) & ":" & Selection(Selection.Count).Address(0, 0) & ")"

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 4

Assuming your selection is one column wide, give this a try...

ActiveCell.Formula = WorksheetFunction.Sum(Range(ActiveCell, Selection(Selection.Count)))

I don't know why you are assigning this sum to the Formula property rather than the Value property, but it will produce the same end result. I am not sure what you mean by "cell link"... can you describe what you are looking for in more detail (perhaps with a example)?

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 5

Hi,

This will enter a formula that sums from activecell+1 to the last used row. Note i include no error checking to check if there are cells below the active cell to sum

LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

Cells(LastRow + 1, ActiveCell.Column).Formula = _

"=SUM(R[-1]C:R[" & -(LastRow - ActiveCell.Row) & "]C)"

EDIT... If you want the formula in the active cell which I don't recommend because there could be data in it use this

LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row

ActiveCell.Formula = _

"=SUM(R[+1]C:R[" & (LastRow - ActiveCell.Row) & "]C)"

---

If this post answers your question, please mark it as the Answer.

Mike H