vba 在 Excel 中将字符串转换为 int 为空或空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13913923/
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
Convert from string to int in Excel for empty or blank
提问by Le Viet Hung
As output I have for Range("H" & temp).Cells:
作为输出，我有Range("H" & temp).Cells：
234
0
(Empty)
2
I want to convert it into longor int, because it's a text value. So I did
我想把它转换成longor int，因为它是一个文本值。所以我做了
Range("H" & temp).Cells = CInt(Range("H" & temp).Cells)
It works perfectly for 234, 0 and 2 but when the cell is empty it shows me error. What should I do? I want the empty cell to be taken as 0, of course using VBA macro.
它适用于 234、0 和 2，但当单元格为空时，它会显示错误。我该怎么办？我希望空单元格被视为 0，当然使用 VBA 宏。
回答by Dick Kusleika
That cell isn't really empty. It has a space or some other non-printing character in it. Try using Trimbefore using CIntto get rid of the spaces.
那个单元格并不是真的空的。它有一个空格或其他一些非打印字符。在使用Trim之前尝试使用CInt以摆脱空格。
回答by Rualark
If you need to do this in formula, you can use this: IF(IFERROR(VALUE(A1)); 0; A1)
如果您需要在公式中执行此操作，则可以使用： IF(IFERROR(VALUE(A1)); 0; A1)
回答by Luis Tellez
Enter the number 1 in an unused cell and copy that cell to the clipboard. Highlight the range of cells in which you want blanks replaced by zeros. Choose Edit | Paste Special from the menu. In the Operation section of the resulting dialog box, select the Multiply option and click on OK.You have just multiplied every cell in the range by 1. Numeric cells won't change. Cells containing text data will be ignored. But any cell that was blank will now contain a zero. You could, of course, accomplish the same thing by copying a cell containing 0 to the clipboard and using Add rather than Multiply in the Paste Special dialog.
在未使用的单元格中输入数字 1，然后将该单元格复制到剪贴板。突出显示您希望用零替换空白的单元格范围。选择编辑 | 从菜单中粘贴特殊。在结果对话框的操作部分，选择乘法选项并单击确定。您刚刚将范围内的每个单元格乘以 1。数字单元格不会改变。包含文本数据的单元格将被忽略。但是任何空白的单元格现在都将包含零。当然，您可以通过将包含 0 的单元格复制到剪贴板并在“选择性粘贴”对话框中使用添加而不是乘法来完成相同的操作。
回答by Larry
Sub t()
Dim colNum As Long
colNum = Range("H1").Column 'please coustomize which column
For i = 1 To 4 'Please customize the row range
Cells(i, colNum).Value = CLng(Cells(i, colNum).Value) 'blank --> 0
Cells(i, colNum).NumberFormat = "0.00" 'also changes the numberFormat
Next i
End Sub
