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

[wk9128]

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

[DocAElstein]

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

...
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

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

In worksheet object code module

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

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