XL:2000 - VBA Sum Formula

  • Thread starter Thread starter Tony
  • Start date Start date
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.
 
One way:

With ActiveCell
.Formula = "=SUM(" & .Offset(-1, 0).End(xlUp).Address(0, 0) _
& ":" & .Offset(-1, 0).Address(0, 0) & ")"
End With
 
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.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
Click to expand...
 
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.
 
Back
Top