如果错误则为空白 [英] If Error Then Blank

问题描述

感谢您查看我的问题.知道我是Stackoverflow和VBA的新手，我正在研究一个很长的宏.在宏的某一点上，我必须将开始深度"(L)和结束深度"(M)从"m"转换为"ft".然后，我将两者相减以找到"Footage"(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

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=60740 and https://superuser.com/questions/715744/excel-2010-formula-how-do-i-write-this-formula-vba

这是我用来将空格更改为0的

Here is what i was using to change blanks into 0's

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

这是导致错误的代码.注意:数据以空格开头，使用这些公式后，由于某些原始单元格以null或空格开头，因此出现错误.而且，这些行不是按此顺序排列的，而是留下错误的行.

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!

推荐答案

这取决于是从公式还是从VBA代码生成 #VALUE！.

It depends if the #VALUE! is being generated from a formula or from VBA code.

如果它是从公式生成的，则将该公式包装在IFERROR函数中.我相信这是在Excel 2007中添加的.例如，如果您的公式是:

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:

=A1-B1

那你可以放

=IFERROR(A1-B1,"")

这是说，如果A1-B1是一个错误，则返回"，否则返回A1-B1的结果.

Which is saying, if A1-B1 is an error, return "", otherwise return the result of A1-B1.

如果该值是由VBA生成的，则可以编写一个类似于IFERROR的辅助函数

If the value is being generated by VBA you could write a helper function that works like 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.

这篇关于如果错误则为空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！