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.

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) & ")"

--
Regards,
Tom Ogilvy

"Judd Jones" <Judd wrote in message
...
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.

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)


"Judd Jones" <Judd wrote in message
...
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.