ISERROR Functin to Much Work!
 

I often use the ISERROR function to wrap around a formual that may have
a an error in it. I use if nested in an If stmt to say if the formual
generates an error, then produce a zero value otherwise, do I want I
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/B1
again at the end of the IF stmt. This is really a pain if the condition
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$550
=
"Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1))

Is there a formual or userdefined function that acts like an iserror
wrapped in an If stm that will allow me to not repeat the formula? It
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?

thnx


--
ExcelMonkey
------------------------------------------------------------------------
ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221
View this thread: http://www.excelforum.com/showthread...hreadid=277709

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
------

use it in your formula as follows:
IF(ISERROR(V(your_formula)),"Error",V())


--
Regards
Frank Kabel
Frankfurt, Germany

"ExcelMonkey" schrieb im
Newsbeitrag ...

I often use the ISERROR function to wrap around a formual that may
have
a an error in it. I use if nested in an If stmt to say if the
formual
generates an error, then produce a zero value otherwise, do I want I
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/B1
again at the end of the IF stmt. This is really a pain if the
condition
is ver large:

=IF(ISERROR(IF(Inputs!$G$550 =

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

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

Is there a formual or userdefined function that acts like an iserror
wrapped in an If stm that will allow me to not repeat the formula?
It
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+Statem
ents!H102+Statements!H106),1)),0)

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

thnx


--
ExcelMonkey
---------------------------------------------------------------------
---
ExcelMonkey's Profile:
http://www.excelforum.com/member.php...fo&userid=5221
View this thread:
http://www.excelforum.com/showthread...hreadid=277709

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

On Fri, 12 Nov 2004 08:29:16 -0600, ExcelMonkey
wrote:


I often use the ISERROR function to wrap around a formual that may have
a an error in it. I use if nested in an If stmt to say if the formual
generates an error, then produce a zero value otherwise, do I want I
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/B1
again at the end of the IF stmt. This is really a pain if the condition
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$550
=
"Denominator",(Statements!H101+Statements!H102 )/(Statements!H101+Statements!H102+Statements!H106), 1))

Is there a formual or userdefined function that acts like an iserror
wrapped in an If stm that will allow me to not repeat the formula? It
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?

thnx