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-programming-vba-macros/1325796-can-the-attachment-be-completely-turned-into-vba-no-more-formula.html



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (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