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 |
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" 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 | |
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" 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 | |
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" 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 |
All times are GMT +1. The time now is 10:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com