A UDF stands for "User Defined Function" which is very similar to a macro but
returns a value instead of executing a set of instructions. It typically
receives arguments like most worksheet functions. It crunches these (if they
exist) and comes up with a result depending on how it is written. You can
then reference the UDF in a cell the same as any function. A very simple
example follows:

Function NegIfTextFound(c As Range, negtext As String) As Integer
NegIfTextFound = IIf(c = negtext, -1, 1)
End Function

If you paste the above to a standard code module you can then reference it
in a cell the same as any function. It requires a cell reference argument
(e.g. D9) and a text argument (e.g. "Unavailable"); and based on analysis of
these arguments returns a result to the cell -- in this case either -1 or 1.

For example, if you want to return to cell B10 either -1 or 1 depending on
whether the cell two columns to the right and one row up contains
"Unavailable" or not, you would enter into B10:
=NegIfTextFound(D9, "Unavailable")

The same relative behaviour exists if you copy/paste or AutoFill.

Instead of a UDF, it seems that what you are doing could be greatly
simplified by using either a "canned" array function such as Sumproduct or a
standard array function. I suggest that you post to the WorksheetFunction ng
instead and describe what you want to do. I expect you will get a much
simpler (shorter) formula. I could likely sort it out myself from your
formula but it's a bit much of a headache for a Saturday night.

As you undoubtedly noticed, I use A1 reference style and am not comfortable
with R1C1.

Best regards,
Greg


"Jim28" wrote:


Thanks for that Greg!

I wish to return a -1 numeric entry and not text! Thanks for pointing
that one out for me!

What is a UDF?


--
Jim28
------------------------------------------------------------------------
Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752
View this thread: http://www.excelforum.com/showthread...hreadid=555297