在 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
现在它运行起来非常好。非常感谢!