Run-Time Error '424' Object Required

[MaxxLevell]

I have the following code that is generating a Run-Time Error '424' Object Required message. The code is actually performing the way I want it to...but with the error every time I enter 'N' in the target column. If 'Y' is entered, it does not present the error. When I select debug, it's highlighting the code that has the blue text. I'm brand new to vba, and the original code was found on this site and modified to correspond with my project.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/18/2020  4:18:03 AM  CDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 19 Then
Dim Lastrow As Long
Dim Lastrow2 As Long

Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
Lastrow2 = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1

Dim ans As Long
ans = Target.Row


If Target.Value = "N" Then
    Rows(ans).Copy Sheets("ICS Not Needed").Rows(Lastrow)
    Rows(ans).Delete
End If
    
If Target.Value = "Y" Then
    Rows(ans).Copy Sheets("ICS Completed").Rows(Lastrow2)
    Rows(ans).Delete
End If
    
End If

End Sub

Thank you for any assistance!

 

[dmt32]

Hi,
untested but see if this update to the code resolves your issue

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Lastrow As Long, Lastrow2 As Long
    Dim ans As Long
    
    On Error GoTo myerror
    
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    
    If Target.Column = 19 Then
        Application.EnableEvents = False
        
        Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
        Lastrow2 = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        
        ans = Target.Row
        
        
        If Target.Value = "N" Then
            Rows(ans).Copy Sheets("ICS Not Needed").Rows(Lastrow)
            Rows(ans).Delete
        End If
            
        If Target.Value = "Y" Then
            Rows(ans).Copy Sheets("ICS Completed").Rows(Lastrow2)
            Rows(ans).Delete
        End If
    End If
    
myerror:
    Application.EnableEvents = True
End Sub

Dave

 

[Joe4]

You may be having a recursion issue.
Note that when using "Worksheet_Change" event procedure, if your code is updating your sheet (i.e. deleting rows, etc), you need to disable the procedure so that the updates made in the code don't call the code to fire again (so it calls itself). Many times, this will lead to unintended consequences. Other times, it could cause you to get caught in an infinite loop.

I was tryping this up when I saw "dmt32" reply. He shows you how to temporarily disable events from running and calling themselves while the code is running by putting the:

       Application.EnableEvents = False

line in your code before any edits are made by the code, and then setting it back with the:

       Application.EnableEvents = True

line after all the changes have been made (if you don't do this, the code won't fire automatically after the first time).

 

[MaxxLevell]

Hi,
untested but see if this update to the code resolves your issue

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Lastrow As Long, Lastrow2 As Long
    Dim ans As Long
   
    On Error GoTo myerror
   
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
   
    If Target.Column = 19 Then
        Application.EnableEvents = False
       
        Lastrow = Sheets("ICS Not Needed").Cells(Rows.Count, 1).End(xlUp).Row + 1
        Lastrow2 = Sheets("ICS Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1
       
       
        ans = Target.Row
       
       
        If Target.Value = "N" Then
            Rows(ans).Copy Sheets("ICS Not Needed").Rows(Lastrow)
            Rows(ans).Delete
        End If
           
        If Target.Value = "Y" Then
            Rows(ans).Copy Sheets("ICS Completed").Rows(Lastrow2)
            Rows(ans).Delete
        End If
    End If
   
myerror:
    Application.EnableEvents = True
End Sub

Dave

Thank you very much...That worked! I marked it as a solution, and will be going through the code you provided to see where I screwed it up. I'm totally new at this, but have a strong interest in learning how to do it correctly.

 

[Joe4]

will be going through the code you provided to see where I screwed it up. I'm totally new at this, but have a strong interest in learning how to do it correctly.

Read my post above for the explanation.
This is very important to know when working with any "Worksheet_Change" event procedure.

 

[MaxxLevell]

You may be having a recursion issue.
Note that when using "Worksheet_Change" event procedure, if your code is updating your sheet (i.e. deleting rows, etc), you need to disable the procedure so that the updates made in the code don't call the code to fire again (so it calls itself). Many times, this will lead to unintended consequences. Other times, it could cause you to get caught in an infinite loop.

I was tryping this up when I saw "dmt32" reply. He shows you how to temporarily disable events from running and calling themselves while the code is running by putting the:

       Application.EnableEvents = False

line in your code before any edits are made by the code, and then setting it back with the:

       Application.EnableEvents = True

line after all the changes have been made (if you don't do this, the code won't fire automatically after the first time).

Thank you for the insight! I was not aware of that at all...but it makes sense. I appreciate you guys help on this.

 

[Joe4]

Thank you for the insight! I was not aware of that at all...but it makes sense. I appreciate you guys help on this.

You are welcome.
Note that you could run into the same situation with a "Worksheet_SelectionChange" event procedure, if your code select any other cells on the worksheet.
So you would just do the same thing.

 

[dmt32]

Thank you very much...That worked! I marked it as a solution, and will be going through the code you provided to see where I screwed it up. I'm totally new at this, but have a strong interest in learning how to do it correctly.

Pleased update worked - You had a recursion issue and setting EnableEvents to False disabled them to stop it repeating.

Appreciate the feedback - glad we were able to resolve

Dave

 

[MaxxLevell]

You are welcome.
Note that you could run into the same situation with a "Worksheet_SelectionChange" event procedure, if your code select any other cells on the worksheet.
So you would just do the same thing.

I'm sure I'll forget that the next time I try to do the same lol...but at least I can come back to this message for a reminder.