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
Selection.Replace doesn't replace commas with a dot
提问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 :
此代码:
- Finds the last row in column 2
- Replaces the commas with dots for each individual cell
- 查找第 2 列中的最后一行
- 用点替换每个单元格的逗号