Insert line sum rows
 

Hi

I have been working on inserting a line whenever the cost centre
changes, then summing the next 10 columns to the right. I have found
some code which inserts a row and sums the like totals of only one
column. Can someone help with extending it out 10 columns. I don't
want to use the subtotal function for a range of reasons. Ta

eg
AA 2 5
AA 2 4
Total 4 9

BB 4 6
BB 4 3
Total 8 9

Regards

Chad

Sub sumrow()
Dim Start As Range, Cell As Range
Set Start = Range("C1")
Set Cell = Range("B2")
Do While Not IsEmpty(Cell)
If Cell.Value < Cell(0).Value Or _
Cell(1, 0).Value < Cell(0, 0).Value Then
Cell.EntireRow.Insert
Cell(0, 2).Formula = "=sum(" & _
Range(Start, Cell(-1, 2)).Address & ")"
Set Start = Cell(1, 2)
End If
Set Cell = Cell(2, 1)
Loop
Cell(1, 2).Formula = "=sum(" & _
Range(Start, Cell(0, 2)).Address & ")"

End Sub

Insert line sum rows
 

Try this

Sub sumrow()

RowCount = 1
Start = RowCount

Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) < Range("A" & (RowCount + 1)) Then
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("A" & (RowCount + 1)) = "Total"
For Colcount = 2 To 11
Cells(RowCount + 1, Colcount).FormulaR1C1 = _
"=SUM(R" & Start & "C:R" & RowCount & "C)"
Next Colcount

RowCount = RowCount + 3
Start = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Chad" wrote:

Hi

I have been working on inserting a line whenever the cost centre
changes, then summing the next 10 columns to the right. I have found
some code which inserts a row and sums the like totals of only one
column. Can someone help with extending it out 10 columns. I don't
want to use the subtotal function for a range of reasons. Ta

eg
AA 2 5
AA 2 4
Total 4 9

BB 4 6
BB 4 3
Total 8 9

Regards

Chad

Sub sumrow()
Dim Start As Range, Cell As Range
Set Start = Range("C1")
Set Cell = Range("B2")
Do While Not IsEmpty(Cell)
If Cell.Value < Cell(0).Value Or _
Cell(1, 0).Value < Cell(0, 0).Value Then
Cell.EntireRow.Insert
Cell(0, 2).Formula = "=sum(" & _
Range(Start, Cell(-1, 2)).Address & ")"
Set Start = Cell(1, 2)
End If
Set Cell = Cell(2, 1)
Loop
Cell(1, 2).Formula = "=sum(" & _
Range(Start, Cell(0, 2)).Address & ")"

End Sub

Insert line sum rows
 

Joel

Thanks that works really well. Really appreciate your help.

Take care

Chad

Insert line sum rows
 

Hi

The above code works really well however I would like to add to it and
am having trouble with the syntax. I would like to minus a sumif
function to the end of the following line.

Cells(RowCount + 1, Colcount).FormulaR1C1 = _
"=SUM(R" & Start & "C:R" & RowCount & "C)"

so Sum - Sumif etc.

I Can't get the R1C1 data to play ball. Anyways I want to say Sum -
Sumif(Range("A" & Start:A & Rowcount, "=Misc", R" & Start & "C:R" &
RowCount & "C)"

With the last part of the sumif being the same criteria as the dynamic
sum range above. Regards

Chad




On Jan 12, 3:15*am, Joel wrote:
Try this

Sub sumrow()

RowCount = 1
Start = RowCount

Do While Range("A" & RowCount) < ""
* If Range("A" & RowCount) < Range("A" & (RowCount + 1)) Then
* * *Rows(RowCount + 1).Insert
* * *Rows(RowCount + 1).Insert
* * *Range("A" & (RowCount + 1)) = "Total"
* * *For Colcount = 2 To 11
* * * * Cells(RowCount + 1, Colcount).FormulaR1C1 = _
* * * * * *"=SUM(R" & Start & "C:R" & RowCount & "C)"
* * *Next Colcount

* * *RowCount = RowCount + 3
* * *Start = RowCount
* Else
* * *RowCount = RowCount + 1
* End If
Loop

End Sub



"Chad" wrote:
Hi

I have been working on inserting a line whenever *the cost centre
changes, then summing the next 10 columns to the right. *I have found
some code which inserts a row and sums the like totals of only one
column. * * Can someone help with extending it out 10 columns. I don't
want to use the subtotal function for a range of reasons. Ta

eg
AA * *2 *5
AA * *2 *4
Total *4 *9

BB * 4 *6
BB * 4 *3
Total 8 *9

Regards

Chad

Sub sumrow()
Dim Start As Range, Cell As Range
Set Start = Range("C1")
Set Cell = Range("B2")
Do While Not IsEmpty(Cell)
* If Cell.Value < Cell(0).Value Or _
* * *Cell(1, 0).Value < Cell(0, 0).Value Then
* * *Cell.EntireRow.Insert
* * *Cell(0, 2).Formula = "=sum(" & _
* * * *Range(Start, Cell(-1, 2)).Address & ")"
* * *Set Start = Cell(1, 2)
* End If
* Set Cell = Cell(2, 1)
Loop
Cell(1, 2).Formula = "=sum(" & _
* Range(Start, Cell(0, 2)).Address & ")"

End Sub- Hide quoted text -

- Show quoted text -