Summing Cells Above

[Steve C]

This should be so simple, but I'm struggling. I want to sum a column of
contiguous numbers and put the total in the first empty cell beneath them.
The number of cells summed varies depending on the column.

When recording this and reviewing the code, the VB Editor gives me the
following:

ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

where -15 is the number of cells to be added. Of course, this changes
depending on the number of cells to be added. I've tried creating a variable
(COUNT) that stores the number of cells to be added and putting it in the
formula as follows, but it only gives me an error message:

ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)"

I'm sure there's an infinitely easier way to do this. Thanks in advance for
any suggestions!

 

[Tom Ogilvy]

If the first cell is always in row 1 then

ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)"

 

[Steve C]

I should have clarified: the first cell is not usually in row one. There
are typically empty cells above each summed range. For example, A1:A5 are
empty, while A6:A23 contain numbers. The sum would be placed in A24 in this
case. The number of summed cells changes from column to column. I know how
to write code to find the first empty cell beneath a column of numbers, but
it's creating the appropriate SUM formula in that cell that's challenging me.
Thanks!
--
Steve C

If the first cell is always in row 1 then

ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy

This should be so simple, but I'm struggling. I want to sum a column of
contiguous numbers and put the total in the first empty cell beneath them.
The number of cells summed varies depending on the column.

When recording this and reviewing the code, the VB Editor gives me the
following:

ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

where -15 is the number of cells to be added. Of course, this changes
depending on the number of cells to be added. I've tried creating a variable
(COUNT) that stores the number of cells to be added and putting it in the
formula as follows, but it only gives me an error message:

ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)"

I'm sure there's an infinitely easier way to do this. Thanks in advance for
any suggestions!

 

[Tom Ogilvy]

R[-1]C

can be used to refer to the last non empty cell. However, it appears the
real challenge is determining where the data starts.

If I wanted to do it I would do

cells(rows.count,activecell.column).End(xlup)(2).Select
set rng = range(activecell.offset(-1,0),activecell.offset(-1,0).End(xlup))
activecell.formula = "=Sum(" & rng.address(0,0), & ")"

--
Regards,
Tom Ogilvy

I should have clarified: the first cell is not usually in row one. There
are typically empty cells above each summed range. For example, A1:A5 are
empty, while A6:A23 contain numbers. The sum would be placed in A24 in this
case. The number of summed cells changes from column to column. I know how
to write code to find the first empty cell beneath a column of numbers, but
it's creating the appropriate SUM formula in that cell that's challenging me.
Thanks!
--
Steve C

If the first cell is always in row 1 then

ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy

This should be so simple, but I'm struggling. I want to sum a column of
contiguous numbers and put the total in the first empty cell beneath them.
The number of cells summed varies depending on the column.

When recording this and reviewing the code, the VB Editor gives me the
following:

ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

where -15 is the number of cells to be added. Of course, this changes
depending on the number of cells to be added. I've tried creating a variable
(COUNT) that stores the number of cells to be added and putting it in the
formula as follows, but it only gives me an error message:

ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)"

I'm sure there's an infinitely easier way to do this. Thanks in advance for
any suggestions!

 

[Steve C]

Thanks, Tom. Your code helped immensely!
--
Steve C

R[-1]C

can be used to refer to the last non empty cell. However, it appears the
real challenge is determining where the data starts.

If I wanted to do it I would do

cells(rows.count,activecell.column).End(xlup)(2).Select
set rng = range(activecell.offset(-1,0),activecell.offset(-1,0).End(xlup))
activecell.formula = "=Sum(" & rng.address(0,0), & ")"

--
Regards,
Tom Ogilvy

I should have clarified: the first cell is not usually in row one. There
are typically empty cells above each summed range. For example, A1:A5 are
empty, while A6:A23 contain numbers. The sum would be placed in A24 in this
case. The number of summed cells changes from column to column. I know how
to write code to find the first empty cell beneath a column of numbers, but
it's creating the appropriate SUM formula in that cell that's challenging me.
Thanks!
--
Steve C

If the first cell is always in row 1 then

ActiveCell.formulaR1C1 = "=Sum(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy



:

This should be so simple, but I'm struggling. I want to sum a column of
contiguous numbers and put the total in the first empty cell beneath them.
The number of cells summed varies depending on the column.

When recording this and reviewing the code, the VB Editor gives me the
following:

ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

where -15 is the number of cells to be added. Of course, this changes
depending on the number of cells to be added. I've tried creating a variable
(COUNT) that stores the number of cells to be added and putting it in the
formula as follows, but it only gives me an error message:

ActiveCell.FormulaR1C1 = "=SUM(R[-COUNT]C:R[-1]C)"

I'm sure there's an infinitely easier way to do this. Thanks in advance for
any suggestions!