VBA在多个单元格选择上相交目标错误

Question

我有一个问题，我使用这个语句(下面)替换指定范围内的任何单元格自动默认为"$0"如果单元格已清空或已删除。问题是，如果我选择多个单元格并删除或使用点击并拖动计算功能来自动填充美元金额，那么我会抛出"类型不匹配";elseif行出错

有人能帮我找到一个解决方案，仍然大量删除或填充一组单元格?

下面的声明。

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Editable")) Is Nothing Then
ElseIf Target = "" Then Target = "$0"
End If
Calculate
End Sub

Answer 1

工作表更改:替换空白单元格

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irg As Range: Set irg = Intersect(Target, Range("Editable"))

If irg Is Nothing Then Exit Sub
Dim urg As Range
Dim iCell As Range
For Each iCell In irg.Cells
If Len(CStr(iCell.Value)) = 0 Then
If urg Is Nothing Then
Set urg = iCell
Else
Set urg = Union(urg, iCell)
End If
End If
Next iCell
If urg Is Nothing Then Exit Sub
Application.EnableEvents = False
urg.Value = "$0"
Application.EnableEvents = True
Calculate ' ?
End Sub