Sum active range

[Guest]

I am trying to write a macro that writes the formula to sum the active cells
in the cell below the acitve. If my range was the same size everytime it
would be simple, but the range is never the same size.

I was trying to store the active range and then recall the range in a
formula, but no luck with this method.

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
set rng = Selection
if rng.Areas.Count > 1 then
msgbox "non contiguous areas selected - exiting"
exit sub
end if
if rng.columns.count > 1 then
msgbox "multiple columns selected = exiting"
Exit sub
end if
set rng1 = rng(rng.count).offset(1,0)
rng1.Formula = "=Sum(" & rng.Address(1,1) & ")"

 

[Bob Phillips]

Hi Judd,

Here is some VBA to create a formula as described

Dim cLastRow As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(cLastRow + 1, "A").Formula = "=SUM(" &
Range("A1").Resize(cLastRow, 1).Address & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)