XL:2000 - VBA Sum Formula

T

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

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
 
D

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
 
J

J.E. McGimpsey

The pieces are:
.Formula = "=SUM(" & _
Click to expand...

just starts the formula: =SUM(
.Offset(-1, 0).End(xlUp).Address(0, 0) _
Click to expand...

. 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.
& ":" &
Click to expand...

Just adds the reference separator.
.Offset(-1, 0).Address(0, 0) & ")"
Click to expand...

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.
 
Top