looking through entire sheet

[NewToVB]

I need to look at every cell in the sheet and if it has "#N/A" then I need to
replace it with "" . I know of a long way to do it (looping through each
cell in each column), but I wanted to see if anyone new of a shorter way to
just look through the whole sheet. So far I have this... and I would do this
for each column. I'm using Visual Studio rather than VBA but its similar.
Thanks!

For i = 1 To lastRow

If oApp.Range("B1:B" & lastRow).Value = "#N/A" Then
oApp.Range("B" & i).Value = ""
End If
Next

 

[David Hilberg]

Since Excel has Goto/Special/Formulas/Errors, you could record a macro
to get you that far, then test each cell in the selection for "#N/A".

- David

 

[David Hilberg]

Here's some tested VBA code that may work for you:

On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 16).Select
For Each c In Selection
If c.Text = "#N/A" Then c.Value = ""
Next c

- David