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

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" wrote in message ps.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
|

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" wrote in message ps.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
|

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" wrote in message
ps.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