# Golf Scoring with a Challenge - UDF

1. ## Golf Scoring with a Challenge - UDF

Hi all
I am trying to create a UDF (user defined function) that will enable me
to sum the golf score for the nine holes BUT with a difference. The
cells for the the nine holes whilst expecting numeric, will also allow
entry of just the following letters; d for DQ (disqualified), n for NR
(no return) and r for Rtd (retired). The UDF therefore must check each
of the 9 hole score and if in any one, there is a letter (say d) then
the total column must read DQ otherwise to sum the score of 9 holes.
Any help would be appreciated.
PS using XP with XL2003.
Rgds
K

2. ## Re: Golf Scoring with a Challenge - UDF

Function GolfScore(a As Range)
Dim i As Long
For i = 1 To a.Cells.Count
If a(i) = "d" Then
GolfScore = "DQ"
Exit Function
End If
If a(i) = "n" Then
GolfScore = "NR"
Exit Function
End If
If a(i) = "r" Then
GolfScore = "Rtd"
Exit Function
End If
GolfScore = GolfScore + a(i)
Next i

End Function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kieranz" <kieran.nayak@gmail.com> wrote in message news:1155563987.213496.62200@i3g2000cwc.googlegroups.com...
| Hi all
| I am trying to create a UDF (user defined function) that will enable me
| to sum the golf score for the nine holes BUT with a difference. The
| cells for the the nine holes whilst expecting numeric, will also allow
| entry of just the following letters; d for DQ (disqualified), n for NR
| (no return) and r for Rtd (retired). The UDF therefore must check each
| of the 9 hole score and if in any one, there is a letter (say d) then
| the total column must read DQ otherwise to sum the score of 9 holes.
| Any help would be appreciated.
| PS using XP with XL2003.
| Rgds
| K
|

3. ## Re: Golf Scoring with a Challenge - UDF

Hi Niek
Many thks, will try out.
Rgds
K

Niek Otten wrote:
> Function GolfScore(a As Range)
> Dim i As Long
> For i = 1 To a.Cells.Count
> If a(i) = "d" Then
> GolfScore = "DQ"
> Exit Function
> End If
> If a(i) = "n" Then
> GolfScore = "NR"
> Exit Function
> End If
> If a(i) = "r" Then
> GolfScore = "Rtd"
> Exit Function
> End If
> GolfScore = GolfScore + a(i)
> Next i
>
> End Function
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Kieranz" <kieran.nayak@gmail.com> wrote in message news:1155563987.213496.62200@i3g2000cwc.googlegroups.com...
> | Hi all
> | I am trying to create a UDF (user defined function) that will enable me
> | to sum the golf score for the nine holes BUT with a difference. The
> | cells for the the nine holes whilst expecting numeric, will also allow
> | entry of just the following letters; d for DQ (disqualified), n for NR
> | (no return) and r for Rtd (retired). The UDF therefore must check each
> | of the 9 hole score and if in any one, there is a letter (say d) then
> | the total column must read DQ otherwise to sum the score of 9 holes.
> | Any help would be appreciated.
> | PS using XP with XL2003.
> | Rgds
> | K
> |

4. ## Re: Golf Scoring with a Challenge - UDF

Here's another way:

Public Function GolfScore(argRange As Range) As Variant
Dim Temp As Variant
Const NumberHolesRequired As Long = 9

If argRange.Rows.Count <> NumberHolesRequired Then
GolfScore = CVErr(xlErrNum)
Exit Function
End If

If (Application.WorksheetFunction.CountIf(argRange, ">=0")) =
NumberHolesRequired Then
GolfScore = Application.Sum(argRange)
Else
GolfScore = "DQ"
End If

End Function

NickHK

"Kieranz" <kieran.nayak@gmail.com> wrote in message
> Hi all
> I am trying to create a UDF (user defined function) that will enable me
> to sum the golf score for the nine holes BUT with a difference. The
> cells for the the nine holes whilst expecting numeric, will also allow
> entry of just the following letters; d for DQ (disqualified), n for NR
> (no return) and r for Rtd (retired). The UDF therefore must check each
> of the 9 hole score and if in any one, there is a letter (say d) then
> the total column must read DQ otherwise to sum the score of 9 holes.
> Any help would be appreciated.
> PS using XP with XL2003.
> Rgds
> K
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1