Selected Answer
Hello Victor and welcome to the forum,
First, I want to tthank you for clearly explaining what you want to achieve and for the sample file which also makes it clear what you gaol is. Your code for Sheet1 is also well done using comments to explain each step. Doing so is very helpful if you (or someone else) need to modify the code a month, 6 months, a year later. It would be great if all posters did as good of a job as you have.
Now to answer your question. There are 3 steps to achieving this. 1) Find the last used row in column "H"; 2) find the MAX value in "H2:H lastrow" and add 1 to it; 3) enter this value in "H" of the target row.
This will require adding 2 variables at the top of your code:
' ==============================
Dim LR As Long ' last used row in column "H"
Dim NewSerial As Long ' serial number which will be entered in column "H" of new row
' ==============================
Then, add these lines before EnableEvents:
~ ~ UPDATED: July 24/23 ~ ~
To eliminate the problem of repeated serial number changes (as described below), two condition checks have been added. 1) do cells A, B, C, and D in target row have data; 2) is there already a serial in H of target row (code below has been updated to nclude these)
' ==============================
' check that cells A, B, C, and D have data >> if not - skip entering serial number
If WorksheetFunction.CountBlank(Range("A" & target.Row & ":D" & target.Row)) > 0 Then GoTo SkipSerial
' check if cell "H" of target row already has a serial >> if so - skip entering serial number
If Range("H" & target.Row) <> "" Then GoTo SkipSerial
' Find Last Used Row In Column " H "
LR = Cells(Rows.Count, 8).End(xlUp).Row
' Determine New Serial Number - - - MAX + 1
NewSerial = WorksheetFunction.Max(Range("H2:H" & LR)) + 1
' Enter New Serial Number Into Column " H "
Range("H" & target.Row) = NewSerial
' serial number is not entered
SkipSerial:
' ==============================
If this solves your problem please mark my answer as selected.
Cheers :-)
