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

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

Joel

Thanks that works really well. Really appreciate your help.

Take care

Chad

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 -