Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sum formula automatically using VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sum formula automatically using VBA
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 "Pmxgs" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sum formula automatically using VBA
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sum formula automatically using VBA
It worked exactly like I wanted.
Thanks a lot "Bob Phillips" wrote: 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 "Pmxgs" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add sum formula automatically | Excel Programming | |||
formula pick up formula from another cell automatically | Excel Worksheet Functions | |||
Replace formula with value automatically. | Excel Worksheet Functions | |||
Run formula automatically | Excel Worksheet Functions | |||
automatically apply formula to new row | Excel Programming |