[NEED HELP] Fix Macro Automatically SUM upper data when entering new data

depchai

New member
Joined
Apr 27, 2016
Messages
2
Reaction score
0
Points
0
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 (please view attached file)

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

Code:
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
Next
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
 

Attachments

  • Auto_Sum_Upper.xlsm
    17.5 KB · Views: 6
  • PN9LBOs.png
    PN9LBOs.png
    76.9 KB · Views: 10
Back
Top