VBA-在命名范围内用 0 替换 #N/A
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43861397/
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
VBA-Replacing #N/A with 0 in a named range
提问by Avi Gupta
I am trying to replace #N/A values in my named range, from a previous operation by using this code:
我正在尝试使用以下代码替换先前操作中命名范围中的 #N/A 值：
For Each cl In m
If cl.Value = "#N/A" Then
Set cl.Value = 0
End If
Next cl
But this is giving me junk output. What am I doing wrong?
但这给了我垃圾输出。我究竟做错了什么？
回答by BuildItBusk
Simpler version:
更简单的版本：
'Set range to whatever you like
Dim rng As Range
Set rng = Worksheets(1).Range("A1:A2")
'Loop all the cells in range
For Each cell In rng
If Application.WorksheetFunction.IsNA(cell) Then
'If cell contains #N/A, then set the value to 0
cell.value = 0
End If
Next
回答by Ambie
The way you check for an error is to use the IsError()function. The variant value won't contain the string "#N/A" although the cell's .Textvalue would (but only if the cell width if big enough). So IsErroris a safer bet. Once you've established that your cell contains an error your next task is to find which one, using the CVErr()function. For completeness I've included all the errors in the sample code below (and an explanation of the functions can be found here: https://msdn.microsoft.com/en-us/library/bb211091(v=office.12).aspx.
检查错误的方法是使用该IsError()函数。变体值不会包含字符串“#N/A”，尽管单元格的.Text值会包含（但前提是单元格宽度足够大）。所以IsError是一个更安全的赌注。一旦您确定您的单元格包含错误，您的下一个任务就是使用该CVErr()函数查找哪个错误。为了完整起见，我在下面的示例代码中包含了所有错误（并且可以在此处找到函数的说明：https: //msdn.microsoft.com/en-us/library/bb211091(v=office.12) .aspx。
Dim cell As Range
Dim v As Variant
Set cell = Sheet1.Range("A1")
v = cell.Value
If IsError(v) Then
Select Case v
Case CVErr(xlErrDiv0)
Case CVErr(xlErrNA)
cell.Value = 0
Case CVErr(xlErrName)
Case CVErr(xlErrNull)
Case CVErr(xlErrNum)
Case CVErr(xlErrRef)
Case CVErr(xlErrValue)
Case Else
End Select
End If
回答by Bathsheba
A few options.
几个选项。
Keep everything on the worksheet (my favourite since then you haven't buried critical functionality in the VBA layer). Use the formula
=IFNA(A1, 0)where A1 contains the cell to test, or=IF(ISNA(A1), 0, A1)in older versions of Excel.Use VBA. If
vis theVariantcontaining the cell value then useIf IsError(v) Then If v = CVErr(xlErrNA) Then 'Set the cell to 0 End If End IfWhere you need the nested block since VBA does not support short-circuited
And, and the test for equality=can cause an error with some variant types.Use the arguably clearest
If Excel.WorksheetFunction.IsNA(v)
将所有内容都保留在工作表上（从那时起我最喜欢的是您没有在 VBA 层中隐藏关键功能）。使用公式
=IFNA(A1, 0)，其中 A1 包含要测试的单元格，或=IF(ISNA(A1), 0, A1)在旧版本的 Excel 中。使用 VBA。如果
v是Variant包含单元格值，则使用If IsError(v) Then If v = CVErr(xlErrNA) Then 'Set the cell to 0 End If End If由于 VBA 不支持 short-circuited
And，因此您需要嵌套块，并且相等性测试=可能会导致某些变体类型出错。使用可以说是最清楚的
If Excel.WorksheetFunction.IsNA(v)
As a final cautionary remark against using a VBA solution for this, note that (2) and (3) have the tendency to obliterate formulas whose parameters depend on cells with values set to #N/Aas #N/Atends to propagatethrough most built-in Excel functions.
作为针对此问题使用 VBA 解决方案的最后警告，请注意，(2) 和 (3) 倾向于删除其参数依赖于值设置为#N/Aas 的单元格的公式，这些公式#N/A往往会通过大多数内置 Excel 函数传播。
回答by Subodh Tiwari sktneer
You may try your code like this...
你可以像这样尝试你的代码......
For Each cl In m
If IsError(cl) Then
cl.Value = 0
End If
Next cl
