时间戳

首页 > 解决方案 > Why doesn't my VBA code work when the selection starts with a dot?

excel - Why doesn't my VBA code work when the selection starts with a dot?

问题描述

我创建了这段代码，如果有的话，它应该用点替换选择中的逗号。但是，如果选择以包含点的单元格开头，则该代码不起作用，但如果它以包含逗号的单元格开头，则该代码有效。如果它以逗号开头，然后是带有点的单元格，然后是带有逗号的单元格，它甚至可以工作。这是代码：

Public Sub DeleteDotsReplaceCommasWithDots()
        
For Each cell In Selection

    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    If InStr(ActiveCell.Value, ".") > 0 And InStr(ActiveCell.Value, ",") > 0 Then
        Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    ElseIf InStr(ActiveCell.Value, ".") = 0 And InStr(ActiveCell.Value, ",") > 0 Then
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormul
    End If
    
Next cell

End Sub

知道为什么会这样吗？谢谢 ：）

标签： excelvbareplace

解决方案

您似乎正在运行您的搜索/替换ActiveCell，而不是您通过For..Each循环循环的每个单元格。With cell.. End With我已经用积木稍微整理了一下。

尝试这个：

Public Sub DeleteDotsReplaceCommasWithDots()
        
    For Each cell In Selection
        With cell
            .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            If InStr(.Value, ".") > 0 And InStr(.Value, ",") > 0 Then
                .Replace What:=".", Replacement:="", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
                .Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            ElseIf InStr(.Value, ".") = 0 And InStr(.Value, ",") > 0 Then
                .Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula
            End If
        End With
    Next cell

End Sub

推荐阅读