如何删除工作表中出现的#N/A？(VBA for excel 需要)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9703473/
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 remove #N/A that appears through out a worksheet? (VBA for excel Required)
提问by user1204868
I have to clear #N/A that happens through out my worksheet when ever i run my code. I'm not sure why and have been debugging for a while but to no avail. What i could do to remedy this problem is to delete it entirely from the page, where they happens randomly. If anyone know how to, do share a VBA code with me.
当我运行我的代码时，我必须清除在我的工作表中发生的#N/A。我不确定为什么并且已经调试了一段时间但无济于事。我可以做些什么来解决这个问题，那就是从页面中完全删除它，它们是随机发生的。如果有人知道如何，请与我分享 VBA 代码。
Codes of doing a simple copy and paste into another sheet
进行简单复制并粘贴到另一张工作表中的代码
thevaluestocopy = Sheets("pivot").Cells(thefirstrow, 1) _
.Resize(thelastrow - thefirstrow, 1)
Worksheets("summary").Cells(3, 16) _
.Resize(thelastrow - thefirstrow + 1, 16) = thevaluestocopy
I have nested that code with different Column because my pivot table changes most of the time. And when i copied for the 2nd time, #N/A appears.. Have no idea why and i believe this works work fine.
我用不同的列嵌套了该代码，因为我的数据透视表大部分时间都在变化。当我第二次复制时，出现 #N/A .. 不知道为什么，我相信这工作正常。
回答by lori_m
You can run this line to remove any #N/Aerror values in the worksheet:
您可以运行此行以删除#N/A工作表中的任何错误值：
Cells.Replace "#N/A","",xlWhole
If the worksheet contains formulas you can try this:
如果工作表包含公式，您可以尝试以下操作：
Cells.SpecialCells(xlCellTypeFormulas,xlErrors).Clear
回答by mattboy
I don't know, but how about doing something like this instead? This is how I generally copy and paste, without problems.
我不知道，但不如做这样的事情怎么样？这就是我通常复制和粘贴的方式，没有问题。
Sheets("pivot").Range("your range").Copy
Worksheets("summary").Range("your range").PasteSpecial
UPDATE If you still want to simply remove all the #N/As with your current code, you can use some code like this.
更新如果您仍然想简单地使用当前代码删除所有#N/As，您可以使用一些这样的代码。
If WorksheetFunction.IsNA(Cells(row, column)) Then Cells(row, column).ClearContents
