仅用文本替换单个数字

仅用文本替换单个数字

在 E 列中,我只想用“DELETE”替换数字 1-9。但是,我的代码也会用两位数替换 1-9,例如 17、28、11。我该如何防止这种情况发生?

Columns("E:E").Select
    Selection.Replace What:="1", Replacement:="DELETE", LookAt:=xlPart,
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="2", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="3", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="4", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="5", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="6", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="7", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="8", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="9", Replacement:="DELETE", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

答案1

在您的场景中,简单地使用 if 语句来确定 1-9 范围内的所有内容似乎更方便。

这个问题的一个简单 VBA 解决方案可能如下所示。首先,将更新范围设置为从 E1 到包含数据的最后一行(在 E 列中)。根据此范围,您只需检查特定单元格是否具有 1-9 范围内的值,如果是,则用 DELETE 替换它,否则保留原始值:

Sub ReplaceValues()
Application.ScreenUpdating = False

Dim ws As Worksheet
Dim UpdateRng As Range

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set UpdateRng = ws.Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    
    For Each cell In UpdateRng
        If cell.Value >= 1 And cell.Value <= 9 Then cell.Value = "DELETE"
    Next cell

Application.ScreenUpdating = True
End Sub

如果您不想依赖 VBA 解决方案,您可以简单地创建一个辅助列,如下所示,并根据需要向下复制以下公式:

=IF(AND(E2>=1,E2<=9),"DELETE",E2)

答案2

我通过添加这段代码解决了我的问题:

Cells.Replace What:="#VALUE!", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

现在它运行起来非常好。非常感谢!

相关内容