User Defined Function for #DIV/0! errors

[zoudazou]

Hello,

I'm new to post, but I've been reading this and many other excel forums for the last 6 months.

I am trying to create a function that will return whatever value is input as long as the value is not #DIV/0!
I don't want to use IFERROR because I still need to see if there are any reference or value errors, but I also don't want to see a bunch of #DIV/0!s all over the place.

This is what I've got so far but when I try to use it, it will result in a #VALUE! error unless the input value is #DIV/0!, in which case it returns 0...like I want it to.

Please Login or Register  to view this content.

Any help would be much appreciated.

[FDibbins]

You could build the following in your formula to look for that specific error. #div/0 has an error code 2...
=ERROR.TYPE(A1)

As far as your VB code is concerned, not too VB-savy here (yet) so this may be a stupid question, but should the Div0 have a "/" in it?

[zoudazou]

Oh, the Div0 in my function is just the name of the function, so it doesn't have to follow naming conventions.

The problem with the ERROR.TYPE() function is that I'd have to add much more. The numbers I'm dividing are pretty large SUMIFS, so I don't want to have to repeat functions.
Using ERROR.TYPE(), it'd have to be something like:
IF(ERROR.TYPE(SUMIFS(A:A,B1,C:C,D1,E:E,F1,G:G,H1)/SUMIFS(I:I,B1,C:C,D1,E:E,F1,G:G,H1))=2,0,SUMIFS(A:A,B1,C:C,D1,E:E,F1,G:G,H1)/SUMIFS(I:I,B1,C:C,D1,E:E,F1,G:G,H1))

I'm just trying to make these as simple as possible so that if I have to hand off this project to anyone else, fixing/modifying stuff will be as easy as possible.

Thanks for commenting!! :D

Edit: I think I see the Div0 that you're talking about. xlErrDiv0 is the VB code for #DIV/0!

[WHER]

See if this does what you're looking for:

[zoudazou]

Update:

I can now get it to display what is input as long as it's NOT #DIV/0!, which is close(er?) to what I wanted:

Please Login or Register  to view this content.

The same thing happens when substituting the xlErrDiv0 with the string "#DIV/0!"

[zoudazou]

That's perfect! Thanks so much, WHER!! :D