如何清除出现在工作表中的#N / A? (VBA for excel必需) [英] How to remove #N/A that appears through out a worksheet? (VBA for excel Required)
问题描述
做一个简单的复制和粘贴到另一个工作表的代码
thevaluestocopy = Sheets(pivot)。Cells(thefirstrow,1)_
.Resize(thelastrow - thefirstrow,1)
Worksheets摘要)。单元格(3,16)_
.Resize(thelastrow - thefirstrow + 1,16)= thevaluestocopy
我已将该代码嵌入到不同的列中,因为我的数据透视表大部分时间都会更改。当我第二次复制时,#N / A出现..不知道为什么,我相信这个工作正常。
您可以运行此行以删除工作表中的任何#N / A
错误值:
Cells.Replace#N / A,,xlWhole
如果工作表中包含公式,可以尝试这样:
Cells.SpecialCells(xlCellTypeFormulas,xlErrors).Clear
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.
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.
You can run this line to remove any #N/A
error values in the worksheet:
Cells.Replace "#N/A","",xlWhole
If the worksheet contains formulas you can try this:
Cells.SpecialCells(xlCellTypeFormulas,xlErrors).Clear
这篇关于如何清除出现在工作表中的#N / A? (VBA for excel必需)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!