Solved: When a row inserted, add one to highest Value in Col N, & add -1 to Col B value

[frank_m]

I have I.D. numbers in Column N that are not necessarily sorted in order.

I Need VBA code that will add the next available I.D. number in Column N when a user inserts a new row at any location.

In other words let's say the highest number found in column N is 16398, and resides in cell N20, and the user inserts a row at row 50, the code will make the N50 cell value 16399

Thanks

[frank_m]

Edit: Row nsertion's are being done with a (custom right click menu) macro, so no need to detect the insertion. (sorry I didn't mention that sooner)

I know this additional request is a lot trickier than the original.

I have Invoice numbers in column B that get larger from bottom to top. When a row is inserted at the top all is simple, but when they are inserted anywhere else, I need this same routine to also look at the column B value and add a -1 to the new row Column B cell.

In other words if a row is inserted at row 50 and the invoice number in Ce11 B49 number is 16340, the Cell B50 value will be 16340-1, or -2 etc, if -1 has already been used.

(I have code that adds the next available invoice number if inserted at row 2, so feel free to either calculate that, or do nothing there)

Edit: Attached a sample workbook

[mdmackillop]

Part 1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ID As Long

If Target.Cells.Count <> Columns.Count Or Application.CountA(Target.EntireRow) > 0 Then Exit Sub
Application.EnableEvents = False
ID = Application.Max(Range("N:N"))
Cells(Target.Row, "N") = ID + 1
Application.EnableEvents = False
End Sub

[mdmackillop]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ID As Long
Dim Inv
If Target.Cells.Count <> Columns.Count Or Application.CountA(Target.EntireRow) > 0 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
ID = Application.Max(Range("N:N"))
Cells(Target.Row, "N") = ID + 1

Inv = Cells(Target.Row - 1, "B")
If InStr(1, Inv, "-") = 0 Then
Cells(Target.Row, "B") = Inv & "-1"
Else
Cells(Target.Row, "B") = Split(Inv, "-")(0) & "-" & Split(Inv, "-")(1) + 1
End If
Exits:
Application.EnableEvents = True

End Sub

[frank_m]

Thanks a bunch mdmackillop

Works very nicely.

Can I convert those targets over to active cells, (or similar), in order to run it from a macro? Or is it not as simple as that?
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ID As Long
Dim Inv
If Target.Cells.Count <> Columns.Count Or Application.CountA(Target.EntireRow) > 0 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
ID = Application.Max(Range("N:N"))
Cells(Target.Row, "N") = ID + 1

'Inv = Cells(Target.Row - 1, "B")'changed to +1 because my numbers grow from bottom to top
Inv = Cells(Target.Row + 1, "B")
If InStr(1, Inv, "-") = 0 Then
Cells(Target.Row, "B") = Inv & "-1"
Else
Cells(Target.Row, "B") = Split(Inv, "-")(0) & "-" & Split(Inv, "-")(1) + 1
End If
Exits:
Application.EnableEvents = True

End Sub

[mdmackillop]

Yes, just change Target to Activecell, or better, enclose in
With ActiveCell
'code
End with
and delete Target where it occurs

[frank_m]

Great.

Thanks again.
Public Sub Insert_Row()
'Right Click Macro
Dim ID As Long
Dim Inv

With ActiveCell
.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

If .Cells.Count <> Columns.Count Or Application.CountA(.EntireRow) > 0 Then Exit Sub
On Error GoTo Exits
Application.EnableEvents = False
ID = Application.Max(Range("N:N"))
Cells(.Row, "N") = ID + 1
'Inv = Cells(.Row - 1, "B")'changed to +1 because my numbers grow from bottom to top
Inv = Cells(.Row + 1, "B")

If InStr(1, Inv, "-") = 0 Then
Cells(.Row, "B") = Inv & "-1"
Else
Cells(.Row, "B") = Split(Inv, "-")(0) & "-" & Split(Inv, "-")(1) + 1
End If

End With
Exits:
End Sub

[frank_m]

All is well. Just updating the code posted here. Not sure how my previous posted code ever worked, (possibly it did so only in my imagination) :yes

Here is the corrected version for anyone that might find it helpful.
Public Sub Insert_Row()
'I run this macro from a customized right click menu
Dim ID As Long
Dim Inv
Dim ValueofCellBelow

Application.EnableEvents = False 'depending on sheet events this may or may not be needed

With ActiveCell

.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'Commented out next line because it is for detecting a row insertion in the Sheet Cnange event
'If .Cells.Count <> Columns.Count Or Application.CountA(.EntireRow) > 0 Then Exit Sub
On Error GoTo Exits

ID = Application.Max(Range("N:N"))
Cells(.Row - 1, "N") = ID + 1

'Inv = Cells(.Row - 1, "B")'removed the -1 because my numbers grow from bottom to top
Inv = Cells(.Row, "B")

If InStr(1, Inv, "-") = 0 Then
Cells(.Row - 1, "B") = Inv & "-1"
Else
Cells(.Row, "B") = Split(Inv, "-")(0) & "-" & Split(Inv, "-")(1) + 1
End If

End With
Exits:
Application.EnableEvents = True' in my previous post I forgot to set back to true
End Sub

[mdmackillop]

You can delete the EnableEvents if you have no event code. It was necessary in my code to prevent the code from looping.