替换文本时如何处理多个活动单元格

Question

我想即时修改单元格的文本。 例如："n"、"N"或"新"--->"新">

我已经让它工作了，但是当我选择多个单元格来删除文本时，我遇到了问题。

有人可以帮助确定我如何实现这一目标并且仍然能够修改多个 excel 单元格值(删除是我唯一关心的问题，除非您能想到我的代码可能导致的其他限制(。

--澄清一下，我的代码将按照我想要的方式更改文本，但是，当我选择多个单元格并按删除键(删除单元格的值(时，我得到 Range(目标地址(的类型不匹配。价值

谢谢大家抽出宝贵时间， 铝

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OriginalText As String
Dim CorrectedText As String
OriginalText = Range(Target.Address).Value
If OriginalText = "r" Or OriginalText = "R" Or OriginalText = "replace" Then
CorrectedText = "Replace"
Range(Target.Address).Value = CorrectedText
ElseIf OriginalText = "a" Or OriginalText = "A" Or OriginalText = "acceptable" Then
CorrectedText = "Acceptable"
Range(Target.Address).Value = CorrectedText
ElseIf OriginalText = "n" Or OriginalText = "N" Or OriginalText = "new" Then
CorrectedText = "New"
Range(Target.Address).Value = CorrectedText
End If
End Sub

更新： 我无法告诉你我有多感谢每个人的慷慨！谢谢你的帮助。我希望这也能帮助其他有需要的人。 干杯，艾尔

Answer 1

使用区域：

Dim area,cell as range
For each area in target.areas
For each cell in area.cells
OriginalText = cell.value
next
next

Answer 2

像这样：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, newText As String, v
'exit if too many cells (eg whole column)
If Target.CountLarge > 1000 Then Exit Sub
For Each c In Target.Cells
v = c.Value
Select Case v
Case "r", "R", "replace": newText = "Replace"
Case "a", "A", "acceptable": newText = "Acceptable"
Case "n", "N", "new": newText = "New"
Case Else: newText = v
End Select
If newText <> v Then
Application.EnableEvents = False
c.Value = newText
Application.EnableEvents = True
End If
Next c
End Sub