Clear Active.Cell IF has error #NAME?

[roc_on_the_rocks]

I would like to clear the content (actually an Excel formula) of the Active.Cell instead of having the cell displaying #NAME?.

It feels easy, but I can't get the syntax right.

Thanks,

 

[roc_on_the_rocks]

It's Excel 2007.

Please help.

 

[jasonb75]

change the formula to

=IFERROR(your existing formula,"")

 

[capparfrank]

Well, this error is occuring because (1) the formula you've typed doesn't exist or is misspelled; or (2) a named reference doesn't exist; or (3) some element of syntax is incorrect.

so, it's not like a good formula was given bad data and barfed -- the formula itself is broken. That seems like the kind of error that you wouldn't want to supress. I'd think that it's the kind of error that you need to fix.

If what you want is the cell to display nothing , then you can do it with IFERROR.

=IFERROR(YourOriginalFormulaHere(),"")

Basically, if there's an error, just fill the cell with an empty string.

But if you actually want to remove the formula completly, then try a macro:

Sub killErrors()
    For Each c In Range("A1:A1")
        If Application.WorksheetFunction.IsErr(c) Then c.Value = ""
    Next c
End Sub

please keep in mind that in both cases these examples will react to ALL errors, not just #Name types.

in order to identify the type of error, you can use

 =Error.Type(a1)

I believe #Name is error type 5. That formula doesn't work with Application.WorksheetFunction, but you could put it in a neigboring cells and work with it from there.

Anyway, good luck.

 

[roc_on_the_rocks]

Many many thanks folks! That will unstuck me.