PDA

View Full Version : URGENT!! Detecting Types of Errors as resulted from Cell Formulas



sorinvoinea
11-13-2007, 09:16 AM
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
11-13-2007, 09:33 AM
=IF(ISERROR(formula)

=IF(ISNA(formula)

=IF(ISREF(formula)

sorinvoinea
11-13-2007, 09:55 AM
I knew about those... sorry! I forgot to mention: I want this from my VBA code module ;) My mistake. Thanks anyway!

rory
11-13-2007, 10:10 AM
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
11-13-2007, 10:44 AM
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
11-13-2007, 01:12 PM
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
11-14-2007, 03:04 AM
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
11-14-2007, 05:22 AM
In what way did figment not understand? In what way did mikerickson not understand?