+ Reply to Thread
Results 1 to 4 of 4

Golf Scoring with a Challenge - UDF

  1. #1
    Kieranz
    Guest

    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. #2
    Niek Otten
    Guest

    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. #3
    Kieranz
    Guest

    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. #4
    NickHK
    Guest

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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