View Full Version : Macro Automatically SUM upper data when entering new data

04-25-2016, 11:25 AM
Hi everybody.
I have a macro, it will automatically add a row and calculate SUM all the data of upper rows when I entering new data.


But if I add or delete any value in Quantity column from any Group, the SUM row will not correct because it does not plus or minus that value.

So I want this macro will calculate exactly when I add more or delete any value.
Please help me fix this macro !!! Thank you!!!! And sorry for my bad English.

Here is my macro

Private Sub Worksheet_Change(ByVal Target As Range)Dim so As String
Dim dg As Long
dg = Target.Row
Application.EnableEvents = False
If Target.Column = 1 And Target.Text <> "" Then
If Target.Row <= 2 Then Exit Sub
For i = Target.Row - 1 To 1 Step -1
If Sheet1.Cells(i, 1) <> "" Then
so = Sheet1.Cells(i, 1)
Exit For
End If
Range(Target.Address, Target.Offset(0, 3).Address).Insert Shift:=xlDown
Sheet1.Cells(dg, 1).EntireRow.Interior.ColorIndex = 35
Sheet1.Cells(dg, 1).EntireRow.Font.Bold = True
Sheet1.Cells(dg, 1).EntireRow.Font.Size = 16
Sheet1.Cells(dg, 1) = "SUM"
Sheet1.Cells(dg, 3).Formula = Application.WorksheetFunction.Sum(Range(Sheet1.Cells(dg - 1, 3), Sheet1.Cells(i, 3)))
Sheet1.Cells(dg + 1, 2).Select
End If
Application.EnableEvents = True
End Sub

04-27-2016, 12:28 AM
anyone please help me :(