Sum Function

[Mikey]

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.

 

[Rick Rothstein]

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?

 

[Mikey]

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.

 

[Mikey]

Sorry for the long response. I need the VBA code to do the Summation and not
paste a number.

 

[Rick Rothstein]

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

 

[Mikey]

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.

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()
Dim myCell As Range
Dim TopCell As Range
Dim BotCell As Range
Dim myRng As Range
Dim myFormulaRng As Range

Set myRng = Selection

For Each myCell In myRng.Cells
If myCell.Row = 1 Then
'do nothing, too high up
Else
If IsEmpty(myCell.Offset(-1, 0).Value) Then
'skip this, too--nothing in the cell above
Else
Set BotCell = myCell.Offset(-1, 0)
If IsEmpty(BotCell.Offset(-1, 0).Value) Then
'only one cell above the cell getting
'the formula
Set TopCell = BotCell
Else
Set TopCell = BotCell.End(xlUp)
End If
Set myFormulaRng = myCell.Parent.Range(TopCell, BotCell)
myCell.Formula = "=sum(" & myFormulaRng.Address(0, 0) & ")"
End If
End If
Next myCell
End Sub

Select the range to get the formulas (more than one area is ok) and see if it
works.

 

[Mikey]

Worked ABSOLUTELY PERFECT!
Don't have a clue what it's doing but . . . it works.
Thank you so much for the time and consideration.

 

[Rick Rothstein]

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)

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.

 

[Rick Rothstein]

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