Creating a Function

Y

yanf7

Hello.
I have a function that I am constantly using.
How can I create my one custom function in Excel 97 so I can use it al
the time?
The function is:
=if(countif($B$2:B2,B2)>1,"D","U")
 
D

Don Guillett

I'm pretty sure this ISN'T what you want but you get the idea
Function ic()
If Application.CountIf(Range("$B$2:B2"), Range("B2")) > 1 Then
ic = "D"
Else
ic = "U"
End If
End Function
 
T

Tom Ogilvy

Do you mean a UDF

Public Function MyCountif(rng As Range, target As Variant)
Dim res As Long
res = Application.CountIf(rng, target)
MyCountif = IIf(res > 1, "D", "U")
End Function



=MyCountif($B$2:B2,B2)

You could probably reduce the arguments, but if you did, then it wouldn't be
recalculated properly. If you made it volatile, it would be calculated too
much and this could be slow if it involves a large range.

--
Regards,
Tom Ogilvy




JMay said:
Tom, Can you suggest something here?
TIA,
Click to expand...
 
J

Jim May

Tom - Thank you for the "right-on" answer to this problem;
I apologize for the subject-line but I was getting flustered
trying to figure this thing out for the OP..
I promise not to do this type thing ("paging Tom O...") again any time
soon..
(as you may have been offended)
Jim May

Tom Ogilvy said:
Do you mean a UDF

Public Function MyCountif(rng As Range, target As Variant)
Dim res As Long
res = Application.CountIf(rng, target)
MyCountif = IIf(res > 1, "D", "U")
End Function



=MyCountif($B$2:B2,B2)

You could probably reduce the arguments, but if you did, then it wouldn't be
recalculated properly. If you made it volatile, it would be calculated too
much and this could be slow if it involves a large range.
Click to expand...
 
T

Tom Ogilvy

I am not offended, but it might prevent someone else who has a good answer
from responding.
 
Top