如何解决我在excel中查找和删除整行代码中的运行时错误13

Question

我正在尝试编写VBA脚本以删除从下拉列表中选择的行。我的代码是

Sub delete_md_entry()
Dim LR As Long
Dim str As Range, rfnd As Range
'LR = Sheets("MASTER_DATA").Range("C2000").End(xlUp).Row
str = Sheets("MASTER_DATA").Range("I2").Value
If Len(str) <> 0 Then
Set rfnd = Selection.Find(str, After:=Range("C5"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not rfnd Is Nothing Then
yes = MsgBox("Do you want to delete" & rfnd.Value & " row?", vbYesNo, "Alert!")
If yes = vbYes Then
rfnd.EntireRow.Delete
Else
Exit Sub
End If
Else
MsgBox "NO ROW FOUND"
End If
Else
MsgBox "Kindly select a name!"
End If
End Sub

不幸的是，它不起作用，并引发错误运行时错误13：类型不匹配。然而，没有选择。我在哪里犯的错？请帮我找出错误！

Answer 1

Find适用于Range，而不是单个单元格Selection。

您需要更改

Set rfnd = Selection.Find(str, After:=Range("C5"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

收件人：

Set rfnd = Range("A2:C10").Find(str, After:=Range("C5"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

当然，您需要将上面的范围("A2:C10"(更改为您正在查看的范围。