URGENT!! Detecting Types of Errors as resulted from Cell Formulas

[sorinvoinea]

I need to detect what kind of error is resulted in a series of cells (each in turn is to be verified) from formulas, if there are any. Like the ones starting with '#' (#DIV/0! etc).
So, first to detect if there is any error, and then if it is, what kind of error is it (this last part with the type you can take it as optional:beerchug:) I only need the function, or algorithms for this task, I dont need any other pieces of code.
And I need it by tomorrow afternooooooon :banghead:
Thanks.

[Bob Phillips]

=IF(ISERROR(formula)

=IF(ISNA(formula)

=IF(ISREF(formula)

[sorinvoinea]

I knew about those... sorry! I forgot to mention: I want this from my VBA code module ;) My mistake. Thanks anyway!

[rory]

You could use something like:
Function GetError(rngInput As Range) As String
Dim varError As Variant
If rngInput.HasFormula Then
varError = Application.Evaluate(rngInput.Formula)
If IsError(varError) Then GetError = rngInput.Text
End If
End Function

[figment]

you could also do

Function GetError(rngInput As Range) As String
If IsError(rngInput) Then GetError = rngInput.Text
End Function


although you should probibly add a return value for no error as well in ether function

[mikerickson]

Look under CVErr in the VBEditor help file.


Sub test()
MsgBox ([1/0] = CVErr(xlErrDiv0))

MsgBox (Application.Match("xx", Range("A:A"), 0) = CVErr(xlErrNA))

Range("a1").FormulaR1C1 = "=LOG(-1)"
MsgBox (Range("a1").Value = CVErr(xlErrNum))

Range("a1").FormulaR1C1 = "=smith"
MsgBox (Range("a1").Value = CVErr(xlErrName))

End Sub

[sorinvoinea]

thanks rory... I will try it now... the rest of you who gave answers thank you also, but maybe it's my fault you didn't understand what my question was about...

[Bob Phillips]

In what way did figment not understand? In what way did mikerickson not understand?