Using a Macro to Sum a Variable-Length Range (a Column)

[Chuckles123]

I created a string formula in the macro to calculate the sum. This
formula includes a reference to a cell, not in the Range, that contains
the row number of the cell that is adjacent above the cell (the Active
Cell) that is to contain the sum formula.

This is the line of code: ActiveCell.FormulaR1C1 = " = ""=sum(J3:J"" &
R[4]C & "")"" " (SPACES ADDED FOR CLARITY). The cell containing the Row
number is 4 rows belows the cell to contain the sum formula.

The macro then does a copy, pastespecial, values on the Active Cell and
the above formula is calculated to be =sum(J3:J23) (ASSUMING THAT 23 IS
THE ROW NUMBER IMMEDIATELY ABOVE THE ACTIVE CELL). Having the macro do
another copy, pastespecial, values on this cell has zero effect.

I tried using Application.SendKeys "{F2}~", but it only wants to open
the Object Browser. If I click on the WorkSheet, key {F2}, then key
{ENTER}, the formula is calculated and the cell displays the numerical
result.

What am I doing wrong?
Chuckles123

 

[Dave Peterson]

You have .formular1c1 in your code--but you put J3:J in the formula.

You're gonna have to use R1C1 style or A1 style--but not a mixture.

I'm confused about what row you're returning, but maybe this'll give you an
idea:

With ActiveCell
.Formula = "=sum(J3:J" & .Offset(4, 0).Value & ")"
.value = .value 'convert to values
End With

I created a string formula in the macro to calculate the sum. This
formula includes a reference to a cell, not in the Range, that contains
the row number of the cell that is adjacent above the cell (the Active
Cell) that is to contain the sum formula.

This is the line of code: ActiveCell.FormulaR1C1 = " = ""=sum(J3:J"" &
R[4]C & "")"" " (SPACES ADDED FOR CLARITY). The cell containing the Row
number is 4 rows belows the cell to contain the sum formula.

The macro then does a copy, pastespecial, values on the Active Cell and
the above formula is calculated to be =sum(J3:J23) (ASSUMING THAT 23 IS
THE ROW NUMBER IMMEDIATELY ABOVE THE ACTIVE CELL). Having the macro do
another copy, pastespecial, values on this cell has zero effect.

I tried using Application.SendKeys "{F2}~", but it only wants to open
the Object Browser. If I click on the WorkSheet, key {F2}, then key
{ENTER}, the formula is calculated and the cell displays the numerical
result.

What am I doing wrong?
Chuckles123

 

[Patrick Molloy]

So, in your example the active cell is J24 and you need the formula in J24
to be =SUM(J2:J23)
also, the '23' is variable, but wikk be the row above the active cell.

DIM Target as Range ' we'll use this to point at the formula cell
SET Target = Range("J3").End(XLDown).offset(1,0)
Target.FormulaR1C1 = "=SUM(R3C:R[-1]C"


if you need to convert the formula to a value, just add
Target.Value = Target.Value