Can the attachment be completely turned into VBA? (No More Formula)

First explain that the formula can complete the task, why not change the VBA reason as follows
The reason is that the formula cannot be used when inserting a row

J16:J34 contains formula,
The formula can be used when J16:J34 delete ROW
When J16:J34 is inserted into ROW, the formula cannot be used because the inserted row cannot be calculated
When inserting a row, there are formulas at the top and bottom, and the formula for inserting the row is gone

I want to turn J16:J34 into VBA without using formula and can insert and delete in J16:J34
The formula for adding J35 is applicable to J16:J34. Inserting and deleting columns does not affect the calculation. I also want to change J35 to VBA.

---

Cross post
https://www.excelforum.com/excel-pro...e-formula.html



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

It is very difficult to understand you. Your English is very bad, so I must try to guess what you want.

I give you two solutions

Solution1 With formulas

Quote:

---

Originally Posted by wk9128

...
When inserting a row, there are formulas at the top and bottom, and the formula for inserting the row is gone.

---

It will not be gone. I will put it back in!

In ThisWorkbook code module

Code:

---

Option Explicit
Private Sub Workbook_Open()
 Let Sheet1.UsdRws = Worksheets.Item(1).UsedRange.Rows.Count
End Sub

---

In worksheet object code module

Code:

---

Option Explicit
Public UsdRws As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.UsedRange.Rows.Count = UsdRws + 1 Then
    Let Application.EnableEvents = False
    Let Range("J" & Target.Row & "").Value = "=IF(OR(RC[-3]="""",RC[-1]=""""),"""",RC[-3]*RC[-1])"
    Let Application.EnableEvents = True
    Else
    End If
End Sub

---

Share 'help0828WithFormula.xlsm' : https://app.box.com/s/2pex879xrgresupqmg4r54wp3e9a016f

---

Solutiuon2 No Formulas
When a row is added, ( or any change made in the worksheet) , the calculation is done, and the sum value pasted to the Total row

In worksheet object code module

Code:

---

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lr As Long
 Let Lr = Range("J" & Rows.Count & "").End(xlUp).Row - 1
Dim arrDta() As Variant
 Let arrDta() = Range("G16:J" & Lr & "").Value2
Dim Cnt As Long
    For Cnt = 1 To UBound(arrDta(), 1) Step 1
    Dim SumJ As Double
        If arrDta(Cnt, 1) <> "" Then
        Let SumJ = SumJ + arrDta(Cnt, 1) * arrDta(Cnt, 4)
        Else
        ' empty row
        End If
   
    Next Cnt
 Let Application.EnableEvents = False
 Let Range("J" & Lr + 1 & "").Value2 = SumJ
 Let Application.EnableEvents = True
End Sub

---

Share 'help0828NoFormula.xlsm' : https://app.box.com/s/ewufwr7p589xf1rq0pt8qys26j6crdxq


Alan