Add sum formula automatically using VBA

[Pmxgs]

Hi,

I'm just starting to learn vba and I'm trying to write a macro that
automatically writes a sum formula at a specific row (row which has the words
"Total dos custos" in column A). This sum formula adds the values of the rows
above which include a sum formula as well.
For example:
At row 34 the cell A34 has the words "Total dos Custos", then from cell b34
until j34 I would like to have a sum formula which sums the cells above which
also have a sum formula.
Supposing that in column B there are already two sums, in rows 12 and 25,
the cell b34 would have the following formula =sum(b12,b25).
In cell c34 the formula would be =sum(c12,c25), etc.
Basically this creates a Grand Total in the row which has the words "Total
dos Custos".

Any help ?

thanks a lot,
Pedro

 

[Bob Phillips]

Sub AddSums()
Dim cell As Range
Dim i As Long
Dim j As Long
Dim tmp As String

Set cell = Columns(1).Find("Total dos Custos")
If Not cell Is Nothing Then

For j = 2 To 10

tmp = ""
For i = 1 To cell.Row - 1

If Left$(Cells(i, j).Formula, 5) = "=SUM(" Then _
tmp = tmp & Cells(i, j).Address(False, False) & ","
Next i

If tmp <> "" Then Cells(cell.Row, j).Formula = "=SUM(" &
Left$(tmp, Len(tmp) - 1) & ")"
Next j
End If
End Sub

 

[Patrick Molloy]

presume B has values? Isn't the formula in C3 just = B2 + C2
and replicate down

in code

Range("C2").Formula = "=B2"
With range(Range(C3),Range(C3).End(xlDown))
.formular1c1 = "=RC2 + R[-1]C"
End with

 

[Pmxgs]

It worked exactly like I wanted.

Thanks a lot

Sub AddSums()
Dim cell As Range
Dim i As Long
Dim j As Long
Dim tmp As String

Set cell = Columns(1).Find("Total dos Custos")
If Not cell Is Nothing Then

For j = 2 To 10

tmp = ""
For i = 1 To cell.Row - 1

If Left$(Cells(i, j).Formula, 5) = "=SUM(" Then _
tmp = tmp & Cells(i, j).Address(False, False) & ","
Next i

If tmp <> "" Then Cells(cell.Row, j).Formula = "=SUM(" &
Left$(tmp, Len(tmp) - 1) & ")"
Next j
End If
End Sub


--
__________________________________
HTH

Bob