vba Selection.Replace 不会用点替换逗号

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/21437372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 01:37:13  来源:igfitidea点击:

Selection.Replace doesn't replace commas with a dot

excelvbareplace

提问by fhbeltrami

I have a column with numbers. If I set it to TEXT format, than select it, click on Replace and replace commas with a dot, it works greatly. My numbers get DOTS instead of COMMAS. (This is what I want, because I am not using their "numeric value").

我有一列数字。如果我将它设置为文本格式,而不是选择它,单击替换并用点替换逗号,它的效果很好。我的数字得到点而不是逗号。(这就是我想要的,因为我没有使用它们的“数值”)。

If I record a macro when doing that, that is the generated code:

如果我在这样做时记录一个宏,那就是生成的代码:

Columns("B:B").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

If I run this code on the same data (the original one, containing COMMAS, it doesn′t work. Why?

如果我在相同的数据上运行此代码(原始数据,包含 COMMAS,它不起作用。为什么?

My data, just in case:

我的数据,以防万一:

1.3.11
4.5.2-714156
3.1.59
4.2.2
4,3
4,3
4,3
4,3

回答by Craig Manson

I'm not necessarily sure why the code you've quoted isn't working. However the following should give you the outcome I believe you're trying to get:

我不确定为什么您引用的代码不起作用。然而,以下应该给你我相信你想要得到的结果:

Dim Cell As Range

For Each Cell In Selection
    Cell.Value = Replace(Cell.Value, ",", ".")
Next

回答by Bernard Saucier

I really can't explain why your code doesn't work, I have played around with it for a few minutes now and I experience the same issues (works while I macro record, doesn't work while I re-execute macro).

我真的无法解释为什么你的代码不起作用,我已经玩了几分钟,我遇到了同样的问题(在我录制宏时有效,在我重新执行宏时无效)。

This, however, succeeds at doing what you want :

然而,这成功地做你想做的事:

last = Cells(Rows.Count, 2).End(xlUp).Row
For x = 1 To last
    Cells(x, 2) = Replace(Cells(x, 2), ",", ".")
Next x

This code :

此代码:

  1. Finds the last row in column 2
  2. Replaces the commas with dots for each individual cell
  1. 查找第 2 列中的最后一行
  2. 用点替换每个单元格的逗号