Continuing Difficulties W/ Sum For Variable-length Column

[Chuckles123]

Dave and Patrick,
Thanks very much for your responses. However, my macro is still no
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 the {F2} ke
from the macro to the WorkSheet (as opposed to opening the Objec
Browser)?

Patrick, I like your approach because it does not require insertion o
the Row number of the cell adjacent above the sum formula in a cel
away from the range to be summed. But I get an "Application-Defined o
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 displa
the row number of the cell adjacent above the sum formula (the Activ
Cell). In your ActiveCell.Formula, the compiler is having difficult
with the .Offset.
I would appreciate your continuing help.
Chuckles123

P.S.: A related issue is the ability to name a variable-length colum
or row in a macro. Any thoughts

 

[Patrick Molloy]

sorry - typo
"=SUM(R3C:R[-1]C)"

I missed the close bracket for the SUM function

please stay in-thread

 

[Bob Phillips]

Variable range

=A1:OFFSET(A1,,,COUNTA(A:A))

 

[Bob Phillips]

I had assumed that you were creating a name to add this as a RefersTo value.

Probably also better to use

=$A$1:OFFSET($A$1,,,COUNTA($A:$A))

--

HTH

RP

Variable range

=A1:OFFSET(A1,,,COUNTA(A:A))

--

HTH

RP

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

P.S.: A related issue is the ability to name a variable-length column
or row in a macro. Any thoughts?