Excel VBA - 数字字段的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9392030/
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
Excel VBA - Value of a field to Number
提问by CustomX
My quote generator creates an Excel document and I want to run a macro on it to just keep it's calculations. For some reason it says 'Number stored as text' when there are numbers. How can I change them to the number format using the macro? I have found out if I click once in the formula bar, the problem is solved too.
我的报价生成器创建了一个 Excel 文档，我想在它上面运行一个宏来保持它的计算。出于某种原因，当有数字时，它会显示“数字存储为文本”。如何使用宏将它们更改为数字格式？我发现如果我在编辑栏中单击一次，问题也解决了。
The code below is what I have now, but it won't solve the error.
下面的代码是我现在所拥有的，但它不会解决错误。
Range("A1:A" & LastRow).Select
Selection.NumberFormat = "0"
Column A contains an amount (1, 2, 3, etc). I have another column with the same problem, but this contains a currency in and has 2 decimal places.
A 列包含数量（1、2、3 等）。我有另一列有同样的问题，但它包含一个货币并有 2 个小数位。
Range("I1:I" & LastRow).Select
Selection.NumberFormat = "0.00"
Thanks for your help! :)
谢谢你的帮助！:)
Extra shoutout to Siddharth who helped me complete this complete issue.
特别感谢 Siddharth，他帮助我完成了这个完整的问题。
回答by Siva Charan
Try this different ways:-
尝试不同的方法：-
Way 1
方式一
Selection.NumberFormat = "General"
Way 2
方式二
vStr = "1000.5"
intNum = CInt(vStr)
Way 3
方式三
Convert Text to Numbers With VBAIf you frequently convert text to numbers, you can use a macro. Add a button to an existing toolbar, and attach the macro to that button. Then, select the cells, and click the toolbar button.
使用 VBA 将文本转换为数字如果您经常将文本转换为数字，则可以使用宏。将按钮添加到现有工具栏，并将宏附加到该按钮。然后，选择单元格，然后单击工具栏按钮。
Sub ConvertToNumbers()
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With
Selection.EntireColumn.AutoFit
End Sub
回答by Siddharth Rout
Tom,
汤姆，
There are 3 ways to solve this
有3种方法可以解决这个问题
1) Go Back to your quote generator and see how it is saving the data to Excel Sheet and amend the code there
1) 返回到您的报价生成器，看看它是如何将数据保存到 Excel Sheet 并修改那里的代码的
2) Manually: Highlight the Range and click on the exclamation mark next to the Green Triangle and click on "Convert to Number" See snapshot
2）手动：突出显示范围并单击绿色三角形旁边的感叹号，然后单击“转换为数字”查看快照
3) Use this code.
3）使用此代码。
Sub Sample()
Dim LastRow As Long, i As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("A1:A" & LastRow).NumberFormat = "0"
For i = 1 To LastRow
If Val(Sheets("Sheet1").Range("A" & i).Value) <> 0 Then _
Sheets("Sheet1").Range("A" & i).Formula = _
Val(Sheets("Sheet1").Range("A" & i).Value)
Next i
Dim temp As Double
LastRow = Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("I1:I" & LastRow).NumberFormat = "$#,##0.00"
For i = 1 To LastRow
If Val(Sheets("Sheet1").Range("I" & i).Value) <> 0 Then
temp = Sheets("Sheet1").Range("I" & i).Value
Sheets("Sheet1").Range("I" & i).ClearContents
Sheets("Sheet1").Range("I" & i).Value = temp
End If
Next i
End Sub
FOLLOW UP
跟进
Before and After snapshot attached
附上之前和之后的快照
MORE FOLLOW UP (IMPORTANT)
更多跟进（重要）
If you have different regional settings then you will have to take care of them appropriately.
如果您有不同的区域设置，那么您必须适当地照顾它们。
For Example Dutch Belgium you have to use "," for a decimal. Please see snapshot.
例如荷兰比利时，您必须使用“,”表示小数。请看快照。
