Clear Cells with #N/A

[isixdos1]

The following is the code I am using:

Sub DailySchedUpdate()
Dim z As Long, e As Long
z = Cells(Rows.Count, 2).End(xlUp).Row
ActiveWindow.SmallScroll Down:=-12
For e = 6 To z

If Cells(e, 1) > 0 Then
Cells(1, 6) = "=VLOOKUP(d" & e & ",'L:\Transfer\Fabrication Department\Data Base\[Fabrication Part Breakdown.xls]Router Breakdown'!$A$2:$G$10000,1,false)"
Cells(e, 6) = Cells(1, 6)
Else
Cells(e, 6) = ""
End If
Next e

Code is working but I need all Cells that yield #N/A cleared. I can't just include "" within the formula due to it crashing another formula. What code can I add to clear the contents of every cell that yields #N/A after running?

 

[Rick Rothstein]

Code is working but I need all Cells that yield #N/A cleared. I can't just include "" within the formula due to it crashing another formula. What code can I add to clear the contents of every cell that yields #N/A after running?

Clear? Do you mean like remove the formula from the cell? If so, just execute this line of code...

Columns("F").SpecialCells(xlFormulas, xlErrors).Clear

 

[isixdos1]

No, I would like for it to clear all cells with the output "#N/A"
I can blank the cell by including "" within the formula but I need to contents cleared because the cell is not truly empty and the output crashes another formula.

 

[Rick Rothstein]

No, I would like for it to clear all cells with the output "#N/A"
I can blank the cell by including "" within the formula but I need to contents cleared because the cell is not truly empty and the output crashes another formula.

You cannot do that with a formula. You will have to let the error show from your formula and then either use VBA code like I posted in Message #2 to clear the cell.

 

[isixdos1]

ADVERTISEMENT

Ok, How do I do it with code?

 

[Rick Rothstein]

Ok, How do I do it with code?

Put the line of code I posted in Message #2 after the "Next e" statement in the code you posted in Message #1.

 

[isixdos1]

ADVERTISEMENT

Put the line of code I posted in Message #2 after the "Next e" statement in the code you posted in Message #1.

Using the code you have supplied the cell values still yield #N/A.
The way my code is running all that remains in the cells once complete is the data I want or #N/A. The formula used in the code is not there.
Does this help?

 

[Rick Rothstein]

The way my code is running all that remains in the cells once complete is the data I want or #N/A. The formula used in the code is not there.
Does this help?

Yes, that helps. Try using this line of code instead of what I posted earlier...

Columns("F").SpecialCells(xlConstants, xlErrors).Clear

 

[isixdos1]

Perfect, you have saved me from pulling what remaining hair I have out.
Many thanks..........