vba 如果错误则为空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26066575/
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
If Error Then Blank
提问by Ryan
Thanks for taking a look at my question. Know that I am new to stackoverflow and VBA I have a long macro I've been working on. At one point in the macro I have to convert "Start Depth" (L) and "End Depth" (M) from "m" to "ft". Then I subtract the two to find the "Footage" (N). However, some of the values in the columns are originally left blank. So, after making my conversions and subtractions, I'm left with "#VALUE!" which is giving me errors later on in the macro. Originally I had changed all of the blanks to 0's before the conversions and subtractions. But, After "finishing" the macro I realize the zeros are messing with presenting the data. So, I'd like to just do the conversions and subtractions and then, change all the "#VALUES!" back to blanks. I found some stuff on this but nothing that I could (that i know of) use or specific to me: http://www.ozgrid.com/forum/showthread.php?t=60740and https://superuser.com/questions/715744/excel-2010-formula-how-do-i-write-this-formula-vba
谢谢你看我的问题。知道我是 stackoverflow 和 VBA 的新手，我一直在研究一个很长的宏。在宏中的某一点，我必须将“开始深度”（L）和“结束深度”（M）从“m”转换为“ft”。然后我将两者相减以找到“素材”（N）。但是，列中的某些值最初留空。因此，在进行转换和减法之后，我只剩下“#VALUE！” 这在以后的宏中给了我错误。最初我在转换和减法之前已将所有空格更改为 0。但是，在“完成”宏之后，我意识到零正在与呈现数据相混淆。因此，我只想进行转换和减法，然后更改所有“#VALUES！” 回到空白。 http://www.ozgrid.com/forum/showthread.php?t=60740和 https://superuser.com/questions/715744/excel-2010-formula-how-do-i-write-this-formula- vba
Here is what i was using to change blanks into 0's
这是我用来将空白更改为 0 的内容
Worksheet1.Select
lastrow = Range("A666666").End(xlUp).Row
For Each cell In Range("A1:Q" & lastrow)
If Len(cell.Value) = 0 Then
cell.Value = 0
End If
Next
Here is the code resulting in errors. Note: The data starts with blanks and after using these formulas I am given errors because some of the original cells begin as null or blanks. Also, These lines aren't in this order but, they are the lines leaving errors.
这是导致错误的代码。注意：数据以空格开头，在使用这些公式后，我得到了错误，因为一些原始单元格以空值或空格开头。此外，这些行不是按此顺序排列的，但它们是留下错误的行。
ActiveCell.FormulaR1C1 = "=CONVERT(RC[2],""m"",""ft"")"
Selection.AutoFill Destination:=Range("N2:O2"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("N2:O" & lastrow)
Range("N1") = "Footage"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("N2:N" & lastrow)
Any help is appreciated. Thanks guys!
任何帮助表示赞赏。谢谢你们！
回答by AndASM
It depends if the #VALUE!is being generated from a formula or from VBA code.
这取决于#VALUE!是从公式生成还是从 VBA 代码生成。
Formula
公式
If it's being generated from a formula, wrap the formula in the IFERROR function. This was added in Excel 2007 I believe. So for example if your formula was:
如果它是从公式生成的，请将公式包装在 IFERROR 函数中。我相信这是在 Excel 2007 中添加的。例如，如果您的公式是：
=A1-B1
Then you could put
然后你可以把
=IFERROR(A1-B1,"")
Which is saying, if A1-B1 is an error, return "", otherwise return the result of A1-B1.
也就是说，如果 A1-B1 是错误，则返回“”，否则返回 A1-B1 的结果。
VBA
VBA
If the value is being generated by VBA you could write a helper function that works like IFERROR
如果该值是由 VBA 生成的，您可以编写一个类似于 IFERROR 的辅助函数
Public Function MyIfError(value As Variant, value_if_error As Variant)
If IsError(value) Then
MyIfError = value_if_error
Else
MyIfError = value
End If
End Function
And then pass your value through that.
然后通过它传递你的价值。
回答by JNevill
This does not directly answer your question, but it does fix the presentation of Zero's on your worksheet. You can use the following setting to show Zeros as Blanks through VBA: ActiveWindow.DisplayZeros = FalseIt might be a consideration instead of looping through everything looking for 0's and switching them to blanks manually.
这不会直接回答您的问题，但它确实修复了工作表上零的显示。您可以使用以下设置通过 VBA 将零显示为空白：ActiveWindow.DisplayZeros = False这可能是一个考虑因素，而不是遍历所有查找 0 并手动将它们切换为空白的内容。
回答by sgp667
Worksheet1.Select
lastrow = Range("A666666").End(xlUp).Row
For Each cell In Range("A1:Q" & lastrow)
If Len(cell.Value) = 0 Then
cell.Value = 0
End If
Next
For Each cell In Range("A1:Q" & lastrow)
cell.value = WorksheetFunction.Iferror(cell.value,"")
End If
Next
I'm sorry I don't have opprtunity to test it right but give it a try.
Bottom line is that I simply included a second loop that runs IFERRORfunction on your data.
对不起，我没有机会正确测试它，但请尝试一下。最重要的是，我只是包含了第二个循环，该循环IFERROR对您的数据运行函数。
