vba 在 Excel 脚本中用点替换逗号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4884480/
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
Replacing commas with dots in Excel script
提问by Peter
I want to replace a comma with dot and vice-versa
我想用点替换逗号，反之亦然
Public Sub VirgulaPunct()
Dim oRow As Range
Dim cell As Range
Dim i As Long, j As Long
Dim MyString As String
Dim aux As String
Application.ScreenUpdating = False
For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1
For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1
MyString = Cells(i, j).Value
For Counter = 1 To Len(MyString)
aux = Mid(MyString, Counter, 1)
If aux = "." Then
MyString = Replace(MyString, ".", ",", 1)
ElseIf aux = "," Then
MyString = Replace(MyString, ",", ".", 1)
End If
Next
Cells(i, j).Value = MyString
Next j
Next i
Application.ScreenUpdating = True
End Sub
回答by Dick Kusleika
You can use the Replace method of the Range object
可以使用 Range 对象的 Replace 方法
Sub ReplacePunct()
Const sTEMPCOMMA = "|comma|"
Const sTEMPDOT = "|dot|"
Const sCOMMA = ","
Const sDOT = "."
If TypeName(Selection) = "Range" Then
With Selection
.Replace sCOMMA, sTEMPCOMMA, xlPart
.Replace sDOT, sTEMPDOT, xlPart
.Replace sTEMPCOMMA, sDOT, xlPart
.Replace sTEMPDOT, sCOMMA, xlPart
End With
End If
End Sub
回答by zosoca
The basic problem is that if the columns is set as general instead of text, even if we changed the "," for "." Excell automatically it will change it again for a ",".
基本问题是，如果将列设置为常规而不是文本，即使我们将“，”更改为“。” Excell 会自动将其再次更改为“，”。
To avoid that it is necessary to set first the column as a Text format, and then we can perform replace function:
为了避免这种情况，需要先将列设置为文本格式，然后我们可以执行替换功能：
This it works for me:
这对我有用：
Worksheets("Name").Activate
Worksheets("Name").Columns(1).Select 'or Worksheets("SheetName").Range("A:A").Select
Selection.NumberFormat = "@"
Dim OriginalText As String
Dim CorrectedText As String
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To LastRow
OriginalText = Worksheets("Name").Cells(i, 1).Value
CorrectedText = Replace(OriginalText, ",", ".")
Worksheets("Name").Cells(i, 1).Value = CorrectedText
Next i
回答by Hans Olsson
This seems to be a continuation to my answer to your previous question, but if so I think you misunderstood what I meant. I've taken your code and amended it with my suggestion, but I've not tested it:
这似乎是我对您之前问题的回答的延续，但如果是这样，我认为您误解了我的意思。我已经采用了您的代码并根据我的建议对其进行了修改，但我还没有对其进行测试：
Public Sub VirgulaPunct()
Dim oRow As Range
Dim cell As Range
Dim i As Long, j As Long
Dim MyString As String
Dim aux As String
Application.ScreenUpdating = False
For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1
For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1
MyString = Cells(i, j).Value
MyString = Replace(MyString, ",", ";+;", 1)
MyString = Replace(MyString, ".", ",", 1)
MyString = Replace(MyString, ";+;", ".", 1)
Cells(i, j).Value = MyString
Next j
Next i
Application.ScreenUpdating = True
End Sub
So as I said in my previous answer, I do 3 calls to Replace, but I do them for the whole string rather than per character in the string.
因此，正如我在之前的回答中所说，我对 进行了 3 次调用Replace，但我对整个字符串而不是字符串中的每个字符进行了调用。
For future reference, it would probably be better if you updated your original question rather than to create a new one and then you could leave a comment for me under my answer and I'd seen that you have done so.
为了将来参考，如果您更新原始问题而不是创建一个新问题可能会更好，然后您可以在我的回答下为我发表评论，我已经看到您这样做了。
回答by Pepe
By looking at your loops you don't seem that you are visiting any cell more than once. Wouldn't a simple if statement work here ?
通过查看您的循环，您似乎不会多次访问任何单元格。一个简单的 if 语句不能在这里工作吗？
if(Cells(i,j).Value = ',')
Cells(i,j).Value = '.'
Elsif(Cells(i,j).Value = '.')
Cells(i,j).Value = ',')
End
回答by Hans Olsson
Coming at it from a VB perspective rather than VBA, I'd look at using the builtin Replacefunction rather than looping through it.
从 VB 而不是 VBA 的角度来看，我会考虑使用内置Replace函数而不是循环遍历它。
So first I'd replace all ,with ;(or if there could be ;in the text I'd use something else, possibly a combination of characters, like ;+;+or similar), then replace all .with ,and finally replace all ;with ..
Probably not very efficient, but very easy.
因此，首先我将全部替换,为;（或者如果;文本中可能有其他内容，我会使用其他内容，可能是字符的组合，例如;+;+或类似的），然后全部替换.为,，最后全部替换;为..
可能不是很有效，但很容易。
