vba 如何在Excel工作表中将列格式化为数字格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9752137/
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
How to format column to number format in Excel sheet?
提问by Punith GP
Below is the VBA code. Sheet2 contains all of the values in general format. After running the code, values in column 'C' of Sheet3 contain exponential values for numbers which are 13 or more digits.
下面是 VBA 代码。Sheet2 包含通用格式的所有值。运行代码后，Sheet3 的“C”列中的值包含 13 位或更多位数字的指数值。
What should be done so that column 'C' of Sheet3 does not contain exponential values?
应该怎么做才能使 Sheet3 的“C”列不包含指数值？
private Sub CommandButton1_Click()
Dim i, j, k As Variant
k = 1
For i = 1 To 30000
If Sheet2.Range("c" & i).Value >= 100 And Sheet2.Range("c" & i).Value < 1000 Then
Sheet3.Range("a" & k).Value = Sheet2.Range("a" & i).Value
Sheet3.Range("b" & k).Value = Sheet2.Range("b" & i).Value
Sheet3.Range("c" & k).Value = Sheet2.Range("c" & i).Value
k = k + 1
End If
Next
End Sub
回答by datatoo
This will format column A as text, B as General, C as a number.
这会将 A 列格式化为文本，B 为常规，C 为数字。
Sub formatColumns()
Columns(1).NumberFormat = "@"
Columns(2).NumberFormat = "General"
Columns(3).NumberFormat = "0"
End Sub
回答by Dick Kusleika
If your 13 digit "number" is really text, that is you don't intend to do any math on it, you can precede it with an apostrophe
如果您的 13 位“数字”确实是文本，即您不打算对其进行任何数学运算，则可以在它前面加上撇号
Sheet3.Range("c" & k).Value = "'" & Sheet2.Range("c" & i).Value
But I don't see how a 13 digit number would ever get past the If statement because it would always be greater than 1000. Here's an alternate version
但我不知道 13 位数字如何超过 If 语句，因为它总是大于 1000。这是一个替代版本
Sub CommandClick()
Dim rCell As Range
Dim rNext As Range
For Each rCell In Sheet2.Range("C1:C30000").Cells
If rCell.Value >= 100 And rCell.Value < 1000 Then
Set rNext = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Offset(1, 0)
rNext.Resize(1, 3).Value = rCell.Offset(0, -2).Resize(1, 3).Value
End If
Next rCell
End Sub
回答by Nabspot
Sorry to bump an old question but the answer is to count the character length of the cell and not its value.
很抱歉碰到一个老问题，但答案是计算单元格的字符长度而不是它的值。
CellCount = Cells(Row, 10).Value
If Len(CellCount) <= "13" Then
'do something
End If
hope that helps. Cheers
希望有帮助。干杯
