Case function

[gbell3587]

Hi

I know how to use Select..case statements in VBA but i dont know how i can write this into a function so that on any given cell, i can use it to calculate a bunch of scenarios

Can someone help?

Thanks
Graham

 

[footoo]

Do you mean a worksheet function or a UDF ?
Give an example of what you want to do.

 

[gbell3587]

Do you mean a worksheet function or a UDF ?
Give an example of what you want to do.

Best description is like a premier league table - each team can get either 0,1 or 3 points depending on their result

So if scores are in cell a1 & b1, for c1 - i want to be able to quickly calculate their points tally for that result.

Thansk
Graham

 

[footoo]

What is the content of A1:C1? Points? Or a score like 5-2?
If a score, how do you know the names of the teams?

 

[gbell3587]

So it would look like this

[TABLE="width: 307"]
<tbody>[TR]
[TD]Red team[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]Blue team[/TD]
[/TR]
[TR]
[TD]Red team[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]Blue team[/TD]
[/TR]
[TR]
[TD]Red team[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]Blue team[/TD]
[/TR]
[TR]
[TD]Red team[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]Blue team[/TD]
[/TR]
[TR]
[TD]Red team[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]Blue team[/TD]
[/TR]
[TR]
[TD]Red team[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]Blue team[/TD]
[/TR]
</tbody>[/TABLE]


B1 & C1 contain each teams score

In cell E1 to E...whatever the end is, i would like to display the points however theres loads of scenarios for points, so im looking to use a select...case function to determine this and plug in all the scenarios

I know how to write select...case code in VBA, but i dont know how to do this as a function that can be used in a bunch of cells

Thanks
Graham

 

[footoo]

I'm not sure I follow what you want but if you want the points for each game shown in column E for the Red team and column F for the Blue team, then

Column E : '=IF(B1>C1,3,IF(B1 < C1,0,1))
<c1,0,1))
<c1,0,1))
<c1,0,1))
<c1,0,1))
And a similar formula in column F, or alternatively : =IF(E1=1,1,3-E1)

If instead you want a UDF using Select Case, the syntax would be the same as for a macro.</c1,0,1))></c1,0,1))></c1,0,1))></c1,0,1))