ISERROR Functin to Much Work!

[ExcelMonkey]

I often use the ISERROR function to wrap around a formual that may hav
a an error in it. I use if nested in an If stmt to say if the formua
generates an error, then produce a zero value otherwise, do I want
want it to. For example:

IF(ISERROR(A1/B1),0,A1/B1)

Problem is that you have to type in the fist part of the formual A1/B
again at the end of the IF stmt. This is really a pain if the conditio
is ver large:

=IF(ISERROR(IF(Inputs!$G$550
"Denominator",(Statements!H101+Statements!H102)/(Statements!H101+Statements!H102+Statements!H106),1)),0,IF(Inputs!$G$55

"Denominator",(Statements!H101+Statements!H102)/(Statements!H101+Statements!H102+Statements!H106),1))

Is there a formual or userdefined function that acts like an iserro
wrapped in an If stm that will allow me to not repeat the formula? I
would look like this:

Iserror2(condition,result if condition is false)
or
Iserror2(A1/B1,0)
or
Iserror2(IF(Inputs!$G$550
"Denominator",(Statements!H101+Statements!H102)/(Statements!H101+Statements!H102+Statements!H106),1)),0)

Does anybody know if this exists or hoe to build this in VBA?

thn

 

[Frank Kabel]

Hi
this ISERROR2 kind of function does not exist directly in Excel. You
can of course create it.
for storing a variable have a look at
http://makeashorterlink.com/?J32E23767
(option 2+3 -> the latter one reposted below)
-----
Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

 

[Gord Dibben]

Tom Ogilvy presented this UDF yesterday.......

Function Noerorvlookup(Lookup As Variant, Rng As Range, Col As Long)
Dim res as Variant
res = Application.VLookup(Lookup, Rng, Col, False)
If IsError(res) Then
Noerorvlookup = 0
Else
Noerorvlookup = res
End If
End Function

OR a macro to add the ISERROR to existing formulas.......

Sub ErrorTrapAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=IF(ISERROR*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=IF(ISERROR(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben Excel MVP