使用 VBA 将特定范围的列从文本格式转换为数字格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18375034/
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
Converting a certain range of columns from text to number format with VBA
提问by EndlessLoop
I get sent a spreadsheet weekly, and for various reasons beyond my control some columns come out as text stored as numbers rather than numbers. I need to convert them to numbers for stuff that happens with them later in the code.
我每周都会收到一份电子表格，由于各种我无法控制的原因，有些列以文本形式存储为数字而不是数字。我需要将它们转换为数字，以便稍后在代码中与它们一起发生的事情。
I am converting them to numbers at the moment by doing this:
我现在通过这样做将它们转换为数字：
Dim rng As Range
For Each rng In Range("A:D").Columns
rng.TextToColumns
Next rng
Is there a better (i.e. more efficient) way of doing this?
有没有更好（即更有效）的方法来做到这一点？
I played around with NumberFormat and it didn't seem to work.
我玩过 NumberFormat 但它似乎没有用。
Thanks in advance (and apologies if I have missed a solution already here - I did a search and didn't find it).
提前致谢（如果我在这里已经错过了解决方案，我深表歉意 - 我进行了搜索但没有找到）。
回答by Ioannis
Excel:
卓越：
- Copy an empty cell
- Select the range in which you need to change the format
- Select
PasteSpecial, and underOperationselectAdd
- 复制一个空单元格
- 选择需要更改格式的范围
- 选择
PasteSpecial，然后在Operation选择下Add
VBA
VBA
(change Sheet1Accordingly, assuming that A100000is empty):
（相应地更改Sheet1，假设A100000为空）：
Sheet1.Range("A100000").Copy
Sheet1.UsedRange.PasteSpecial , xlPasteSpecialOperationAdd
If you place the above into the Workbook_Openevent, it will perform the conversion automatically every time you open the workbook.
如果将上述内容放入Workbook_Open事件中，则每次打开工作簿时它都会自动执行转换。
With this method, formulas are preserved.
使用这种方法，公式得以保留。
I hope that helps!
我希望这有帮助！
回答by Helfenstein
From this website http://www.access-programmers.co.uk/forums/showthread.php?t=219316I got this idea:
从这个网站http://www.access-programmers.co.uk/forums/showthread.php?t=219316我得到了这个想法：
Range("D1:D400") = Range("D1:D400").Value
And it works perfectly!
它完美地工作！
回答by DeepikaShinde
This is easier:
这更容易：
With Selection
.NumberFormat = "0"
.Value = .Value
End With
