Error 1004 - Delete method of range class failed

[Rfriend]

I have code I copied from a tutorial that is no longer available. When I use the code I get the 1004 error "Delete Method of Range Class Failed. I have nailed it down to th this line: findvalue.EntireRow.Delete, but I can't figure out why or how to get passed it. My worksheet is an employee roster. THe search is looking for a record number returned from a search to a list box in a user form. I double click the record and it fills 30 controls in the user form. I should then be able to delete the record selected based on the record number in control "Emp1". The code requires that the position number "Emp4" and record number "Emp1" be present to proceed.

The code is posted below. Any education and resolution is very welcomed at this point.

Thank you

Private Sub cmdDeleteEmp_Click()
'declare the variables
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
'error statement
    On Error GoTo errHandler:
'check for values
    If Emp1.Value = "" Or Emp4.Value = "" Then
        MsgBox "There is no data to delete"
        Exit Sub
    End If
'give the user a chance to change their mind
    cDelete = MsgBox("Are you sure that you want to delete this record", vbYesNo + vbDefaultButton2, "click OK to detete this record")
    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
       findvalue.EntireRow.Delete  'error line
    End If
'clear the controls
    cNum = 30
    For x = 1 To cNum
        Me.Controls("Emp" & x).Value = ""
    Next
'run the filter
    AdvFilterOutdata
'add the values to the listbox
    lstEmployee.RowSource = ""
    lstEmployee.RowSource = "Outdata"
'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub

 

[Rfriend]

Hopefully this is what you need.

 

[rlv01]

I'm puzzled that we appear to be talking at cross-purposes. Did you try my recommendations in post #10?

 

[Rfriend]

I'm puzzled that we appear to be talking at cross-purposes. Did you try my recommendations in post #10?

Thanks for trying to help. At this point I will just junk this and rewrite. To clarify, I am not having a problem searching for anything, my issue is deleting the record (table row) returned to the controls after selecting from the listbox. I guess I am not clear on what you need. Thanks again.

 

[MARK858]

@Rfriend, What happens with the change below?

    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=CLng(Emp1), LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
       End If
    End If

 

[rlv01]

Back post #2, I posted this recommended code:

    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
       End If
    End If

Then in post #5 you posted this:

On closer inspection, I see your code contains an error.

You are using:

If findvalue Is Nothing Then

When you should be using

If Not findvalue Is Nothing Then

Try making that change and re-run

 

[Rfriend]

Back post #2, I posted this recommended code:

    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
       End If
    End If

Then in post #5 you posted this:

View attachment 88330

On closer inspection, I see your code contains an error.

You are using:

If findvalue Is Nothing Then

When you should be using

If Not findvalue Is Nothing Then

Try making that change and re-run

 

[Rfriend]

Back post #2, I posted this recommended code:

    If cDelete = vbYes Then
'find the row
        Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
          Exit Sub
       End If
    End If

Then in post #5 you posted this:

View attachment 88330

On closer inspection, I see your code contains an error.

You are using:

If findvalue Is Nothing Then

When you should be using

If Not findvalue Is Nothing Then

Try making that change and re-run

I have the correct code but still get the same error in the same place. Emp1 id the record ID selected upon double clicking a selected record. Error msg states it can't find the record number, but the number is there and exists in emp1 control.




Code I am using with your changes:

Option Explicit
Dim cNum As Integer
Dim x As Integer

Private Sub cmdDeleteEmp_Click()
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
        On Error GoTo cmdDeleteEmp_Error:
    If Emp4.Value = "" Then
        MsgBox "There is no data to delete"
        Exit Sub
    End If
        cDelete = MsgBox("Are you sure that you want to delete this record", vbYesNo + vbDefaultButton2, "Click to confirm")
    If cDelete = vbYes Then

    Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If Not findvalue Is Nothing Then
          findvalue.EntireRow.Delete  'error line
        Else
          MsgBox "Cannot find record " & Emp1.Value
        Exit Sub
        End If
    End If
        
'findvalue.EntireRow.Delete
    cNum = 33
        For x = 1 To cNum
            Me.Controls("Emp" & x).Value = ""
        Next
    Unprotect_All
        AdvFilterArchive
        lstEmployee.RowSource = ""
        lstEmployee.RowSource = "OutData"
        On Error GoTo 0
    Protect_All
        Exit Sub
cmdDeleteEmp_Error:
    Protect_All
        MsgBox "Error" & Err.Number & " (" & Err.Description & ") in procedure cmdDeleteEmp _Click of Form EmployeeDB"
End Sub

 

[rlv01]

"<Out of Context>" means the macro was not executing when you took the snapshot. You need to set a breakpoint and run the macro.

 

[rlv01]

Not Found vs. Found.

 

[rlv01]

Change this

Set findvalue = ActiveSheet.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)

To this

Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1.Value, LookIn:=xlValues)