By the way, you can easily loop through several column, applying the
AddSumFormula subroutine along the way. For example, a macro something like
this (assuming the StartRow is defined internal to the AddSumFormula
subroutine so it does not have to be specified)...

Sub PlaceSeveralSUMs()
Dim C As Variant
For Each C In Array(3, 5, "J", "L")
AddSumFormula C
Next
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a general subroutine that you can call whenever you want to place
a SUM formula at the end of a column...

Sub AddSumFormula(Col As Variant, StartRow As Long)
Dim SumRow As Long
Const WorksheetName As String = "Sheet1"
With Worksheets(WorksheetName)
SumRow = .Cells(.Rows.Count, Col).End(xlUp).Row + 1
.Cells(SumRow, Col).Formula = "=SUM(" & .Range(.Cells(StartRow, Col), _
.Cells(SumRow - 1, Col)).Address & ")"
End With
End Sub

Change the assigned worksheet name from my example "Sheet1" to the name of
your actual worksheet's name. To use this subroutine from within your own
code, just call it and pass the column letter or number as the first
argument and the row number with your first piece of data as the second
argument. So, if you wanted to place the SUM formula at the end of Column
J and the first piece of data in starts in Row 2, you would include this
statement in your own macro...

AddSumFormula "J", 2

Note that if all your data starts in the same fixed row, then you can
remove the 2nd argument from the AddSumFormula subroutine and hard-code it
inside the body of the subroutine instead. For example, if your data
always starts in Row 2 (just under a header row), then the AddSumFormula
subroutine can be written this way instead...

Sub AddSumFormula(Col As Variant)
Dim SumRow As Long
Const StartRow As Long = 2
Const WorksheetName As String = "Sheet1"
With Worksheets(WorksheetName)
SumRow = .Cells(.Rows.Count, Col).End(xlUp).Row + 1
.Cells(SumRow, Col).Formula = "=SUM(" & .Range(.Cells(StartRow, Col), _
.Cells(SumRow - 1, Col)).Address & ")"
End With
End Sub

Then to call this from your own macro, all you would have to do is specify
the column letter or number as the only argument...

AddSumFormula "J"

--
Rick (MVP - Excel)


"Mikey" wrote in message
...
I don't want to use Named Ranges in the Summation formula. I may have a
1,000 or more cells where I need to add the Summation code to and that
would
create another problem. I can get the VBA code to move to the blank cell
where I want to add the formula but don't know the VBA code to Sum Up the
data in the continuous cells above it. That's what I need the code to do
for
me, give me a non-specific range reference for the Sum function. The
columns
are from say J thru V; 12 months & a total column. I hope this explains
it a
little better. If not let me know and I'll keep trying.

Thanks for the responses.
--
Mickey


"Rick Rothstein" wrote:

If I understand what you want correctly, assigning this to the Formula
property of the cell you want to contain the summation should do it...

"=SUM(MyRange)"

where MyRange is the name of the range that you assigned from the
NameBox or
via the Insert/Name/Define dialog box. If you have the named range
assigned
to a variable (named, say, NamedRange) in your code, then you would use
this
instead...

"=SUM(" & NamedRange & ")"

--
Rick (MVP - Excel)


"Mikey" wrote in message
...
I need the formula. I can get the value by naming the selected range
and
then using the Sum("namedrange") function. I have to copy/paste
values to
be
able to reuse the named range on addition lines in the worksheet.
With
the
formula, I can copy it to cells adjacent to it cells (monthly totals
and a
yearly total) and then move down the worksheet to Sum additional
products
or
items. Thanks for the interest as this is really important to me to
be
able
to use for multiple application.
--
Mickey


"Rick Rothstein" wrote:

Your question is not entirely clear to me. You say "formula" and that
you
will "copy and repeat the code" throughout the "worksheet"... are you
looking for a formula or VB code in the end? If VB code, do you want
it
to
insert a summation formula or do you want it to perform the summation
and
just enter that value in the total cell? Is this summation formula or
value
being placed in the cell immediately after the last value in a
column?
What
columns are we talking about?

--
Rick (MVP - Excel)


"Mikey" wrote in message
...
I'm trying to Sum various numbers of rows in a large worksheet. I
need
a
genric formula to sum the continuous rows of numbers above the
total
line
where the formula will be located. The number of continuous rows
for
each
instance will be different, therefore the need for the general
reference
or
generic formula. I've tried the Range(Selection,
Selection.Offset(-1,0)
and
ActiveCell.Offset (-1,0) and also the Selection(xlEndUp) codes but
can't
get
them to produce the Sum function correctly. I can copy and repeat
the
code
throughout the worksheet if only I can figure out how to do the
first
one.
Help please. I've tried to doulbe click the Sum button but it
gives me
the
specific range at the time and is not usable on other rows farther
down
in
the worksheet.
--
Mickey