Hi
Please try better to read and understand the answers you are given.
We are all starting to waste time.
Originally Posted by
wk9128
...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
Code:
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….
Code:
' 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 …
Originally Posted by
DocAElstein
......
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
Code:
Option Explicit
Private Sub Workbook_Open()
Let Sheet1.UsdRws = Worksheets.Item(3).UsedRange.Rows.Count
End Sub
The main macro is modified at beginning
Code:
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
Reply With Quote
Bookmarks