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

[Chuckles123]

Dave and Patrick,
Thanks very much for your responses. However, my macro is stil
not working.
I appreciate the comment about mixing relative and absolute addressin
in my formula; but it appears to be a non-fatal error. I say thi
because I was able to get the formula = SUM(J3:J99) in the appropriat
cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was abl
to get the numerical result to display.
Which, part of my original question, is why can I not send th
{F2} key from the macro to the WorkSheet (as opposed to opening th
Object Browser)?
Patrick, I like your approach because it does not requir
insertion of the Row number of the cell adjacent above the sum formul
in a cell away from the range to be summed. But I get a
"Application-Defined or object-defined error" message when using you
Target.FormulaR1C1 = "=SUM(R3C:R[-1]C".
Dave, I am using the row 4 rows down from the sum formula t
display the row number of the cell adjacent above the sum formula (th
Active Cell). In your ActiveCell.Formula, the compiler is havin
difficulty with the .Offset.
I would appreciate your continuing help.
Chuckles12

 

[Dave Peterson]

Actually, it isn't the absolute versus relative ($A$1 vs A1) that's causing the
trouble--it's the mixture of R1C1 and A1 that's trouble.

Sendkeys works against the application that's got focus--and it sounds like the
VBE had focus. You could have been more unlucky. You could have been in some
application that used F2 to do something drastically wrong--most people don't
use it, 'cause they can't rely on it.

The ".value = .value" would serve as the equivalent of F2, F9, and enter. (I
thought you wanted the value.)

This line:
..Formula = "=sum(J3:J" & .Offset(4, 0).Value & ")"

Stars in the activecell, goes down 4 rows (same column) and uses the value found
in that cell in the formula.

Isn't that what you wanted?

If no, you could play with the .offset(4,0). The first number is the number of
rows, the second is the number of columns.

(I'm still confused about what cell should be used.)

Dave and Patrick,
Thanks very much for your responses. However, my macro is still
not working.
I appreciate the comment about mixing relative and absolute addressing
in my formula; but it appears to be a non-fatal error. I say this
because I was able to get the formula = SUM(J3:J99) in the appropriate
cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was able
to get the numerical result to display.
Which, part of my original question, is why can I not send the
{F2} key from the macro to the WorkSheet (as opposed to opening the
Object Browser)?
Patrick, I like your approach because it does not require
insertion of the Row number of the cell adjacent above the sum formula
in a cell away from the range to be summed. But I get an
"Application-Defined or object-defined error" message when using your
Target.FormulaR1C1 = "=SUM(R3C:R[-1]C".
Dave, I am using the row 4 rows down from the sum formula to
display the row number of the cell adjacent above the sum formula (the
Active Cell). In your ActiveCell.Formula, the compiler is having
difficulty with the .Offset.
I would appreciate your continuing help.
Chuckles123