删除几行中的值时出现 VBA 错误：运行时错误"13"：类型不匹配

Question

我的VBA代码有问题。通常，每一行代码都能正常工作，但如果我试图删除E列中至少两行的值(甚至是空单元格((选择并删除(，我会得到

运行时错误"13"：类型不匹配

我读到这是因为没有声明变量，但我不知道缺少了什么。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCellsKolumnE As Range
Set KeyCellsKolumnE = Range("E2:E100")
If Not Application.Intersect(KeyCellsKolumnE, Range(Target.Address)) _
Is Nothing Then
If Range(Target.Address).Value = "TEXT1" _
Or Range(Target.Address).Value = "TEXT2" Then
Range(Target.Address).Offset(, 3).Value = "TEXT3"
ElseIf Range(Target.Address).Value = "TEXT4" _
Or Range(Target.Address).Value = "TEXT5" _
Or Range(Target.Address).Value = "TEXT6" Then
Range(Target.Address).Offset(, 3).Value = "TEXT7"
ElseIf Range(Target.Address).Value = "TEXT7" Then
Range(Target.Address).Offset(, 3).Value = "TEXT7"
Range(Target.Address).Offset(, 10).Value = "TEXT8"
ElseIf Range(Target.Address).Value = "" Then
Range(Target.Address).Offset(, 3).Value = ""
Else
Range(Target.Address).Offset(, 3).Value = ""
End If
End If
End Sub

Answer 1

正如BigBen所指出的，主要问题应该是多单元Target，它需要一个循环

此外，您可能希望确保多单元Target完全位于列E 内

我还将If ElseIf EndIf语法转换为Select Caseone

最后，我引入了良好的编码实践，以避免在这样的事件处理程序中进行多次递归调用

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCellsKolumnE As Range
Set KeyCellsKolumnE = Range("E2:E100")
If Not Application.Intersect(KeyCellsKolumnE, Target) Is Nothing And Target.Columns.CountLarge = 1 Then ' make sure Target is completely inside column E
On Error GoTo SafeExit
Application.EnableEvents = False ' disable events to prevent recursive calling
Dim cel As Range
For Each cel In Target ' loop through each Target cell
With cel ' reference current cell
Select Case .Value
Case "TEXT1", "TEXT2"
cel.Offset(, 3).Value = "TEXT3"
Case "TEXT4", "TEXT5", "TEXT6"
.Offset(, 3).Value = "TEXT7"
Case "TEXT7"
.Offset(, 3).Value = "TEXT7"
.Offset(, 10).Value = "TEXT8"
Case Else
.Offset(, 3).ClearContents
End Select
End With
Next
End If
SafeExit:
Application.EnableEvents = True ' restore events back
End Sub