Using VBA to sum rows

[rammi125]

I have a list of data, column A is a unique identifier, column B is a number. I already wrote a macro that every time the value in column A changes to insert three rows. I then manually go to column B and sum the total of the rows.

The number of rows for each unique identifier as well as the number of identifiers each day will be different. Any thoughts on the code to write to automatically sum the rows automatically?

 

[Kamolga]

Hi,

Do you want the sum of the three rows (in A) in B? Could you post the macro and expected outcome?

 

[rammi125]

every time the value changes in column A the macro inserts three rows. On the second row I sum the rows above it for column B. I pasted the outcome below as well as my current code to insert three rows once column A changes. The paste looks like I'm summing column B in column A, but I am summing column B in column B.



a 1
a 1
a 1

3

b 3
b 3
b 3
b 3
b 3

15


c 7
c 7

14






Sub InsertRowAtChangeInValue()

Dim lRow As Long


For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then
Rows(lRow).EntireRow.Insert
Rows(lRow).EntireRow.Insert
Rows(lRow).EntireRow.Insert

End If
Next lRow
End Sub

 

[Kamolga]

Maybe...

Sub InsertRowAtChangeInValue()

    Dim lRow As Long
[COLOR=#0000ff]    Dim Rng As Range
[/COLOR]
    For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
                    If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then
                       Rows(lRow).EntireRow.Insert
                       Rows(lRow).EntireRow.Insert            
[COLOR=#0000ff]                       Set Rng = Range(Cells(lRow-2, 2), Cells(lRow-2, 2).End(xlUp))
                       Cells(lRow,2)= Application.WorksheetFunction.Sum(Rng)[/COLOR]
                       Rows(lRow).EntireRow.Insert
                    End If
    Next lRow

 End Sub

 

[rammi125]

It works except doesn't sum the last set of data. I get the following error: run-time error '1004'. Application-defined or object-defined error at the following line: Set Rng = Range(Cells(lRow - 2, 2), Cells(lRow - 2, 2).End(xlUp))

 

[Kamolga]

It works except doesn't sum the last set of data. I get the following error: run-time error '1004'. Application-defined or object-defined error at the following line: Set Rng = Range(Cells(lRow - 2, 2), Cells(lRow - 2, 2).End(xlUp))

Maybe code below then

Sub InsertRowAtChangeInValue()

    Dim lRow As Long
[COLOR=#0000ff]       Dim Rng As Range[/COLOR]
           For lRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row To 2 Step -1
                    If Cells(lRow, "A") <> Cells(lRow - 1, "A") Then
                       Rows(lRow).EntireRow.Insert
                       Rows(lRow).EntireRow.Insert 
                       [COLOR=#0000ff]On Error resume next   [/COLOR]        
[COLOR=#0000ff]                         Set Rng = Range(Cells(lRow-2, 2), Cells(lRow-2, 2).End(xlUp))
                         Cells(lRow,2)= Application.WorksheetFunction.Sum(Rng)[/COLOR]
                       Rows(lRow).EntireRow.Insert
                    End If
    Next lRow
 [COLOR=#0000ff]  Dim lr as long
   lr=Cells(Rows.Count, "A").End(xlup).Row
   Set Rng = Range(Cells(lr, 2), Cells(lr, 2).End(xlUp)) 
                         Cells(lr+2,2)= Application.WorksheetFunction.Sum(Rng)[/COLOR]
 End Sub

 

[rammi125]

Thank you so much. When I use the code above i'm getting a sum of 2 after the header which doesn't make sense. Also the first set of data the sum is not correct, it should be 3 not 2?


Security Amount

2

a 1
a 1
a 1

2

b 3
b 3
b 3
b 3
b 3

15

c 7
c 7

14