Creating a Function
 

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

--
Message posted from http://www.ExcelForum.com

Creating a Function
 

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

--
Don Guillett
SalesAid Software

"yanf7 " wrote in message
...
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 all
the time?
The function is:
=if(countif($B$2:B2,B2)1,"D","U"))


---
Message posted from http://www.ExcelForum.com/

Paging Tom Ogilvy,,,
 

Tom, Can you suggest something here?
TIA,

"yanf7 " wrote in message
...
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 all
the time?
The function is:
=if(countif($B$2:B2,B2)1,"D","U"))


---
Message posted from http://www.ExcelForum.com/

Paging 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" wrote in message
news:c2sZc.231397$Oi.105625@fed1read04...
Tom, Can you suggest something here?
TIA,

"yanf7 " wrote in message
...
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 all
the time?
The function is:
=if(countif($B$2:B2,B2)1,"D","U"))


---
Message posted from http://www.ExcelForum.com/

Paging Tom Ogilvy,,,
 

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" wrote in message
...
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" wrote in message
news:c2sZc.231397$Oi.105625@fed1read04...
Tom, Can you suggest something here?
TIA,

"yanf7 " wrote in message
...
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
all
the time?
The function is:
=if(countif($B$2:B2,B2)1,"D","U"))


---
Message posted from http://www.ExcelForum.com/

Paging Tom Ogilvy,,,
 

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

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:WiOZc.233222$Oi.217931@fed1read04...
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" wrote in message
...
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" wrote in message
news:c2sZc.231397$Oi.105625@fed1read04...
Tom, Can you suggest something here?
TIA,

"yanf7 " wrote in message
...
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
all
the time?
The function is:
=if(countif($B$2:B2,B2)1,"D","U"))


---
Message posted from http://www.ExcelForum.com/