Try recording a macro when you:

Select the range
Edit|goto special (or F5 or ctrl-g)
Special
Formulas
Uncheck Numbers, text, logicals, but keep Errors checked.

Then hit the delete key on the keyboard.

Stop recording

This will clean those #n/a's along with #ref!'s, div/0, ..., well, all those
errors!

Jan Kronsell wrote:

I have a lot of cells returning #N/A! as a result of failed VLOOKUP's.
I would like to change all of these to "blanks", using

Range("A1:B70").Copy
Range("A1:B70").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'replace "#N/A" with "replword"
Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

The first part transforms the erro codes into a value, displayed as #N/A
without the exclamationmark from the code. The latter part does absolutely
nothing, but leaves the cells as is.
When i use this code on any of the cells,

Sub t()
Dim a As Variant
a = ActiveCell.Value
Debug.Print a
End Sub

Ir returns Error 2042. If I try to replace "#N/A" in the replace statement
with "Erro 2042" still nothing happens as Error 2042 apparently is not a
value.

So how do I replace the formulas returning #N/A! with nothing?

Jan

--

Dave Peterson