Run-time error '424': Object requird

**Intern2** · 12-02-2013, 08:29 AM

Hi all,

I'm a newby on VBA and have spend many hours writing the following code, but still it doesn't seem to work. Marked in red gives error '424', can anyone help me out with this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Dim LR As Long
If LCase(Target) = "not approved" Then GoTo notapproved2
If LCase(Target) = "approved" Then GoTo approved1
Exit Sub

notapproved2:
If LCase(Target) = "not approved" Then
With Sheets("not approved")
LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(LR, 1)
Target.EntireRow.Delete
End With
End If
Application.EnableEvents = True

approved1:
If LCase(Target) = "approved" Then
With Sheets("approved")
LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(LR, 1)
Target.EntireRow.Delete
End With
End If
Application.EnableEvents = True

**Norie** · 12-02-2013, 08:34 AM

When you delete Target.EntireRow you delete Target, so when the code reaches here Target doesn't exist.

If LCase(Target) = "approved" Then

Try this.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long

    If Target.Column <> 1 Then Exit Sub

    Application.EnableEvents = False

    Select Case LCase(Target)

        Case "not approved"
            With Sheets("not approved")
                LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                Target.EntireRow.Copy Destination:=.Cells(LR, 1)
                Target.EntireRow.Delete
            End With

        Case "approved"
            With Sheets("approved")
                LR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                Target.EntireRow.Copy Destination:=.Cells(LR, 1)
                Target.EntireRow.Delete
            End With

    End Select

    Application.EnableEvents = True

End Sub

PS Can you add code tags?

**Intern2** · 12-02-2013, 08:37 AM

Norie, thank you so very much! It works perfect!

Run-time error '424': Object requird

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Run-time error '424': Object requird

Re: Run-time error '424': Object requird

Re: Run-time error '424': Object requird

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Simple Calendar pop up macro --> error Run-time error '424': Object required

[SOLVED] Error " Run-time error '1004': application defined or object defined error

Run-time error '-2147417848 (80010108)':Automation error.The object invoked has disconnect

Appropriate Formula requird..

[SOLVED] run-time error '1004': Application-defined or object-deifined error

Bookmarks

Bookmarks

Posting Permissions