Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Defined functions (2002)

    I have defined a function that in a sports scoring program converts data in a single cell to a form needed for the results.

    In effect RESULT = bsecs(G6) + bsecs(G7)

    I now find I want to do the same addition over a range of 20 cells.

    Because of the function bsecs() I can’t work with a range in the conventional way
    SUM(G6:G26)

    If I try SUM(bsecs(G6):bsecs(G26)) #VALUE! is shown

    For it to work I have to use
    RESULT = SUM(bsecs(G6),bsecs(G7),bsecs(G8) and so on for 20 cells)

    Have I missed something simple here?
    Do I really have to have 20 entries for a range of 20 contiguous cells?
    Or is it my fault for defining functions?

    Tony
    Rutland, UK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Defined functions (2002)

    One possibility is to enter the formula

    =bsecs(G6)

    in cell H6, and fill down to H26. Then, in the cell where you want the sum, enter

    =SUM(H6:H26)

    Another possibility would be to write a new function

    Function sumbsecs(oRange As Range)
    Dim oCell As Range
    For Each oCell In oRange.Cells
    sumbsecs = sumbsecs + bsecs(oCell)
    Next oCell
    End Function

    and use the formula

    =sumbsecs(G6:G26)

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined functions (2002)

    Thanks Hans

    Your function suggestion did the job for me.

    You provided the answer within 30 minutes, what a service!

    Thanks again,
    Tony

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Defined functions (2002)

    You might try this minor modification. In this one the ranges do not have to be contiguous:

    <pre>Function SumbSecs2(ParamArray oRange())
    Dim i As Integer
    Dim oCell As Range
    For i = LBound(oRange) To UBound(oRange)
    For Each oCell In oRange(i)
    SumbSecs2 = SumbSecs2 + bsecs(oCell)
    Next oCell
    Next i
    End Function</pre>


    With this function, you could also use something like:
    <pre>=sumbsecs2(B1:B4, B6, B8:B10)</pre>


    Steve

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Defined functions (2002)

    Steve

    I will tuck that one away for future use.

    Arrays and loops in functions were both out side my experience.

    So thanks for the extra information.

    Tony

Posting Permissions

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