Reserve the Horizontal line numbers and information , but the calculation result is change

[DocAElstein]

Hi
Please try better to read and understand the answers you are given.
We are all starting to waste time.

...There is a small problem that has not been resolved.....

They are not small problems. You are asking for lots of work to be done for you!

---

Issue 1
…ITEM serial number is established according to QTY, if QTY is empty, ITEM serial number is also empty
The sequence number is from small to large, from top to bottom, as long as you delete or add row, it will automatically adjust….

This section must be replaced

        If Range("A" & Target.Row & "").Value2 = "" Then   '  We are only intertsted in putting a value in column A to update an ittem number if there is not already one in there
        ' get current maximum item number info: what is it, and where is it
        Dim Cnt As Long, Mx As Long, MxInd As Long ' MxInd is for the Item number at which we got the maximum , - I need this to know where to put the new  ITEM
        Dim RngA As Range: Set RngA = Range("A16:A34")
            For Cnt = 1 To RngA.Rows.Count Step 1
                If Mx < RngA.Item(Cnt).Value Then ' In Excel Ranges cell item numbers are counted  along columns then next rows  etc. So for a single column, each next item number is the next row
                 Let Mx = RngA.Item(Cnt).Value
                 Let MxInd = Cnt
                Else
                
                End If
            Next Cnt
        ' update current row item number to be the current highest, and make previous highest one more
         Let Application.EnableEvents = False ' I have to temporarily turn this thing off, or else the next line makes this macro start again
         Let Range("A" & Target.Row & "").Value2 = Mx: Let RngA.Item(MxInd).Value2 = Mx + 1
         Let Application.EnableEvents = True
        Else
        ' Column A already has a number in so no item number update
        End If
    

Instead we just order… The sequence number is from small to large, from top to bottom….

    ' Dynamic Lr
    Dim Lr As Long: Let Lr = Range("J" & Rows.Count & "").End(xlUp).Row - 1
        If Range("A" & Target.Row & "").Value2 = "" Then   '  We are only intertsted in putting a value in column A to update an ittem number if there is not already one in there
         Let Application.EnableEvents = False
         Let Range("A" & Target.Row & "").Value2 = "anything" ' Put anything in for now
         Let Application.EnableEvents = True
        Dim RngA As Range: Set RngA = Range("A16:A" & Lr & "")
        Dim Cnt As Long, ACel As Range
            For Each ACel In RngA.SpecialCells(xlCellTypeConstants) ' Each cell with something in it in column A
             Let Cnt = Cnt + 1
             Let Application.EnableEvents = False
             Let ACel.Value = Cnt  ' The next cell down is given the next number
             Let Application.EnableEvents = True
            Next ACel
        Else
        ' Column A already has a number in so no item number update
        End If
    

---

Other issues ?????
I will try to guess…

For example …

......
For example, the ranges used in the macro are fixed. They can be made dynamic, ....

See Lr in latest macro

Inserting rows ( to include formula also is there )
Solution is like Solution1 here https://excelfox.com/forum/showthrea...ll=1#post14856
In ThisWorkbook code module

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

The main macro is modified at beginning

Public UsdRws  As Long
Public Sub Worksheet_Change(ByVal Target As Range)
    If Me.UsedRange.Rows.Count = UsdRws + 1 Then ' We added a row
     Let Application.EnableEvents = False
     Let Range("J" & Target.Row & "").Value = "=IF(OR(RC[-3]="""",RC[-1]=""""),"""",RC[-3]*RC[-1])"
     Let Application.EnableEvents = True
     Let UsdRws = UsdRws + 1
     Exit Sub ' No more will be done after a row insert
    Else
    End If
    

---

Latest macro is for 3rd worksheet ( worksheet "Sheet2 excelforum jindon" ) in file:
Share 'help0824_2.xls' : https://app.box.com/s/6sln43gyk68mjv0wskedtrfit40flm6r
Macro is also here: https://excelfox.com/forum/showthrea...ll=1#post14855

---

If you want more help in the future from excelfox, then please try to take more time to prepare your question carefully, and please take more time to try to read and understand what is written for you

Alan