我制作了一個 vba 腳本，應該將特定區域的值從逗號分隔更改為點分隔。
這是代碼：
Sub commas_to_periods()
'
' commas_to_periods Macro
' Convert all commas to Periods in a specified area that contains values before converting the file to csv
'
'
Range("U3:AJ139").Select
' Selection.Replace What:=",", Replacement:=".", SearchOrder:=xlByColumns, MatchCase:=True
' Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
' SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
' ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Dim V As Variant, i As Long, j As Long, T
V = Range("U3:AJ139").Value
howMany = 0
Application.ScreenUpdating = False
T = Timer
For i = LBound(V, 1) To UBound(V, 1)
For j = LBound(V, 2) To UBound(V, 2)
If InStr(V(i, j), ",") > 0 Then
V(i, j) = Replace(V(i, j), ",", ".")
howMany = howMany 1
End If
Next j
Next i
Range("U3:AJ139").Value = V
msgForBox = "That took " & Timer - T & " Seconds and found: " & howMany & " commas"
MsgBox (msgForBox)
Application.ScreenUpdating = True
End Sub
如您所見，我嘗試了幾種方法，但我還使用了該自定義回圈來計算替換次數。我就是無法讓它作業！訊息框顯示它找到了 100 多個逗號，但是當宏完成時沒有進行任何更改。下次我運行它時，我會在框中收到完全相同的訊息。找到相同數量的逗號 - 沒有變化！
If I try to replace anything else, it works just fine. Replacements are made. When I try it with the comma, it stubbornly doesn't work. Now I suppose it has something to do with my regional settings but I can't figure out a workaround. When I do a manual search and replace the commas get changed. I even recorded a macro of me making a manual search and replace and I got the same result. It seems that VBA reverts my changes to comply with my regional settings. Is there a way to go around that?
uj5u.com熱心網友回復：
請注意，如果您的區域設定設定,為小數點，這意味著任何包含 a 的數字.都將被視為文本。
因此，如果您希望 Excel 不將它們轉換為數字，則需要確保單元格的數字格式設定為文本：.NumberFormat = "@"在寫入包含.點的數字之前。
例如
Sub a()
Selection.NumberFormat = "@"
Selection = Replace(Selection, ",", ".")
End Sub
對于多個單元格，您需要回圈：
Sub b()
Dim Rng As Range
Set Rng = Selection
Rng.NumberFormat = "@"
Dim Cell As Variant
For Each Cell In Rng.Cells
Cell.Value2 = Replace$(Cell.Value2, ",", ".")
Next Cell
End Sub
因為任何其他解決方案都不起作用，因為 Excel 認為它很聰明并將任何文本轉換回數字。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/440538.html