XL:2000 - VBA Sum Formula

[Tony]

I have written the following code in VBA to sum the
relative range of cells above any current cell position:

ActiveCell.Offset(-1, 0).End(xlUp).Name = "TopOfRange"
ActiveCell.Offset(-1, 0).Name = "BottomOfRange"
ActiveCell.Formula = "=sum(TopOfRange:BottomOfRange)"

How can I copy this cell across the row (ie. 10 times) so
that the appropriate range above each cell position is
summed (ie. if the above formula appears in A10 which
sums A1:A9, then if the formula is copied to B10, it sums
B1:B9)?

Thanks in advance.

 

[J.E. McGimpsey]

One way:

With ActiveCell
.Formula = "=SUM(" & .Offset(-1, 0).End(xlUp).Address(0, 0) _
& ":" & .Offset(-1, 0).Address(0, 0) & ")"
End With

 

[Dana DeLouis]

Would this idea help? It assumes you want to sum beginning in Row 1:

Sub Demo()
[A10].Resize(1, 10).FormulaR1C1 = "=Sum(R1C:R[-1]C)"
End Sub

 

[Tony]

J.E. McGimpsey:
Your formula is outstanding! Thank you very much.
Can you please explain how it works.
Specifically, 1) how does the formula work within the " &
xxx & " and 2) what function does the Address(0,0) serve
within the formula.
Thank you in advance.

-----Original Message-----
One way:

With ActiveCell
.Formula = "=SUM(" & .Offset(-1, 0).End (xlUp).Address(0, 0) _
& ":" & .Offset(-1, 0).Address(0, 0) & ")"
End With

 

[J.E. McGimpsey]

The pieces are:

.Formula = "=SUM(" & _

just starts the formula: =SUM(

.Offset(-1, 0).End(xlUp).Address(0, 0) _

. stands for the object specified in the With...End With
structure, in this case ActiveCell. So it's equivalent to

ActiveCell.Offset(-1, 0).End(xlUp).Address(0, 0)

which is the address of the cell that is at the top of the
contiguous range of values or blanks, starting one row above the
active cell. The (0, 0) after address specifies that the address
should have both rows and columns in relative addressing, not
absolute. See XL/VBA Help - it's in there.

& ":" &

Just adds the reference separator.

.Offset(-1, 0).Address(0, 0) & ")"

refers to ActiveCell.Offset(-1, 0).Address, again in relative
addressing.

Putting them together produces a relative addressing formula that
sums from the top of the column of values (assuming no blanks) to
the cell just above the activecell. You can then copy this to left
or right and the addresses will change accordingly.