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

 

[rlv01]

Keep in mind that when using the range Find method, youhave to allow for the case of the find operation not finding anything. Example.

    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

 

[Rfriend]

Keep in mind that when using the range Find method, youhave to allow for the case of the find operation not finding anything. Example.

    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

It still gives the same error at the same line "findvalue.entirerow.delete

 

[rlv01]

Is your worksheet protected? Because attempting to delete a row on a protected worksheet will produce that error.

 

[Rfriend]

Is your worksheet protected? Because attempting to delete a row on a protected worksheet will produce that error.

Yes, I forgot to put that back into the code when I reinstalled it. This solved the original issue, but now when clicking the "Delete" button it errors at the msgbox "Cannot find record " & Emp1.value. When I double click the record the Emp1 & Emp4 controls are populated with the correct data, but it errors as stated above.

Private Sub cmdDeleteEmp_Click()
    Dim findvalue As Range
    Dim cDelete As VbMsgBoxResult
    Dim cNum As Integer
    On Error GoTo errHandler:
    If Emp1.Value = "" Or 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 OK to detete this record")
    If cDelete = vbYes Then
    Set findvalue = Sheet7.Range("A:A").Find(What:=Emp1, LookIn:=xlValues)
        If findvalue Is Nothing Then
          findvalue.EntireRow.Delete
        Else
          MsgBox "Cannot find record " & Emp1.Value 'error
          Exit Sub
        End If
    End If
    cNum = 30
    For x = 1 To cNum
        Me.Controls("Emp" & x).Value = ""
    Next
Unprotect_All
    AdvFilterOutdata
    lstEmployee.RowSource = ""
    lstEmployee.RowSource = "Outdata"
    On Error GoTo 0
    Protect_All
    Exit Sub
errHandler:
Protect_All
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub

 

[rlv01]

but now when clicking the "Delete" button it errors at the msgbox "Cannot find record " & Emp1.value.

That means your .Find(What:=Emp1, LookIn:=xlValues) operation could not find anything. Nothing found means you cannot delete anything.
What is the value of Emp1.value? Is it in Col A? What cell?

 

[Rfriend]

That means your .Find(What:=Emp1, LookIn:=xlValues) operation could not find anything. Nothing found means you cannot delete anything.
What is the value of Emp1.value? Is it in Col A? What cell?

Column A Row 9. Search criteria is E5:E6
Starting after header row.

 

[rlv01]

In my earlier post I asked you what the value Emp1.value was for your failed search, but you did not answer. What was it?

Search criteria is E5:E6

If your range variable (Emp1) that you are using as a search term is assigned to two cells (E5:E6) , that could be a problem. You will either need to reduce it to just one cell, or else add code to build your search term from the contents of the two cells.

 

[Rfriend]

In my earlier post I asked you what the value Emp1.value was for your failed search, but you did not answer. What was it?



If your range variable (Emp1) that you are using as a search term is assigned to two cells (E5:E6) , that could be a problem. You will either need to reduce it to just one cell, or else add code to build your search term from the contents of the two cells.

I apologize, I thought I did. The search criteria is the record number in column A of the attachment in my prior post. Once you double click a record in the search results, the controls are populated. You then can edit, or delete, the record loaded into the controls. This actually worked when I built it, but now doesn't, and I can't figure out why.

 

[rlv01]

The search criteria is the record number in column A

No it is not. Col A are the fields being searched. That it is not the search term. An example of the search term would be "109".