Using the SUM Formula

[Dave]

I'm creating a macro that uses a selected form with several preinserted
sections (with a header, two item descriptions, then costing totals at the
bottom of each section). At the very bottom of the form, I have the Form
Totals, which adds up all the totals from each section above.

My question is, the macro will be used to insert a new section just above
the total line. How do I write the VBA code (using Excel 2000), to insert
the formula on the Section Total Line for each of the 4 columns in the
section.

For example:

Item Description 1 100 200 300 400
Item Description 2 250 250 450 850

Section Total 350 450 750 1250

I need to use the SUM so I can add more descriptions individually later.

I know this has been done many times before, but I'm having trouble figuring
it out. Any help would be appreciated. Thanks.

 

[GaryDK]

Hi Dave,

Assuming you have one row between your second description and section
total rows, this macro will do what you describe. Select the first
total cell and then run the macro.

Sub SumSection()
With ActiveCell
.FormulaR1C1 = "=SUM(R[-3]C:R[-2]C)"
.Copy Destination:=.Offset(0, 1).Resize(1, 3)
.Offset(1, -1).Select
Application.CutCopyMode = False
End With
End Sub

Also, if all your section totals have the label "Section Total", you
can have your Form Totals cells automatically update when you insert a
new section. With the following asumptions (just for this example):

1. Your form starts in column A, and the costing numbers are in B
thru E.
2. The Form Totals line is in row 20.
3. The Form Totals formulas encompass the rows in which you insert a
new section.

Then you can use this formula in the first Forms Total cell (and copy
it to the others):

=SUMIF($A$1:$A$19,"Section Total",B1:B19)

I hope this helps,

Gary