Creating a Function

[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")

 

[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

 

[JMay]

Tom, Can you suggest something here?
TIA,

 

[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

Tom, Can you suggest something here?
TIA,

 

[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

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.

 

[Tom Ogilvy]

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