Runtime Error when inserting new row into table.

Hello,

I have a small VBA macro which generates a unique employee ID based on whether they are full employees, from temp agency A and temp Agency b. I have attached a sample spreadsheet showing what I have thus far. The code is as follows...

Private Sub Worksheet_Change(ByVal Target As Range)
   'Do nothing if more than one cell is changed or content deleted

   If Target.Cells.Count > 1 Or IsEmpty(Target) Or Target.Column <> 2 Or Not IsEmpty(Target.Offset(0, 1)) Then
    Application.EnableEvents = True
    Exit Sub
   End If

          'Stop any possible runtime errors and halting code

            On Error Resume Next

                 Application.EnableEvents = False
'
' Add 1 to current maximumn for this employee group
'
                Target.Offset(0, 1) = Application.VLookup(Target.Value, Range("Employee_tbl"), 3, 0) + 1
                
                'Turn events back on

                Application.EnableEvents = True

            'Allow run time errors again

            On Error GoTo 0

       
     

End Sub

Sub x()
Application.EnableEvents = True

End Sub

Currently I can create a new row at the bottom of the table with no error. However when I attempt to insert a new row in the middle of the table, my VBA throws runtime error: 1004. Please assist me troubleshooting this problem as I am extremely new to VBA.

Thank you in advance.

Jekeith,

Split your "If" codeline to the following

    '....
    If Target.Cells.Count > 1 Then Exit Sub
    If IsEmpty(Target) Or Target.Column <> 2 Or Not IsEmpty(Target.Offset(0, 1)) Then

    '....

HTH,
Maud

That did it for me. If you don't mind my asking...why did that make a difference?

Hu Jekeith,

"Target.Offset(0, 1)" is not defined when making a change to an entire row (insert). Splitting the code line allows "If Target.Cells.Count > 1 Then Exit Sub" to process and exit the routine before the following line containing "Target.Offset(0, 1)" can execute.

HTH,
Maud