Results 1 to 9 of 9

Thread: Best way to skip 1st & last cell in range in UDF?

  1. #1
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Best way to skip 1st & last cell in range in UDF?

    I have a little UDF that does some tallies on a column of cells (B2:B5).

    R/C A B
    1 Group Rating
    2 North 25.2
    3 South 14.7
    4 East 52.0
    5 West 33.8
    6 Average 31.4

    I call the UDF from another cell like this:
    Code:
    =Tally(B2:B5)
    I want to change that to include the cell above the first data cell (B1) and the cell after the last data cell (B6) so if I add a row, I don't have to edit the UDF call.
    Code:
    =Tally(B1:B6)
    But I want the UDF to skip over these boundary cells. Here's my code, which works.
    Code:
    Public Function Tally(Ratings As Range) As String
    
    Dim cell As Range
    Dim i As Integer
    
    i = 0
    For Each cell In Ratings
      i = i + 1
      If (i = 1) Or (i = Ratings.Count) Then
        GoTo Continue
      End If
         . . . do the tallies . . .
    Continue:
    Next cell
    
    . . . complete the calculations and return the results . . .
    
    End Function
    Is there a better way to do this?

    Thanks
    Using Office 2007 Pro on Win XP Pro

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    You left out the most important part to us, what is the UDF actually doing?
    What are these "tallies"?
    Was is it returning?
    Is it summing? Averaging?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    How about
    Code:
    Public Function Tally(Ratings As Range) As String
    
    Dim i As Long
    
    For i = 2 To Ratings.Rows.Count - 1
       Debug.Print Ratings(i).Address
    '     . . . do the tallies . . .
    Next i
    
    '. . . complete the calculations and return the results . . .
    
    End Function
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    Quote Originally Posted by Joe4 View Post
    You left out the most important part to us, what is the UDF actually doing?
    What are these "tallies"?
    Was is it returning?
    Is it summing? Averaging?
    It's doing all of that and more. But what difference does that make? I want the UDF to step through each of the cells in the range skipping the first one and the last one?
    Using Office 2007 Pro on Win XP Pro

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    I am just trying to figure out if there is a way to avoid the loop altogether (i.e. if we could use something like the AVERAGE or SUM) function.
    But of course, we do not know if we can avoid the loop until we understand what kind of calculations are being done in the loop.

    Loops are very inefficient, so if you are truly concerned about "speeding things up" or "being more efficient", you want to avoid loops, if at all possible.
    Last edited by Joe4; Oct 8th, 2019 at 10:44 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Public Function Tally(Ratings As Range) As String
    
    Dim i As Long
    
    For i = 2 To Ratings.Rows.Count - 1
       Debug.Print Ratings(i).Address
    '     . . . do the tallies . . .
    Next i
    
    '. . . complete the calculations and return the results . . .
    
    End Function
    Perfect. And I can get the value in each cell as text using:
    Code:
    Ratings(i).Text
    Thank you
    Using Office 2007 Pro on Win XP Pro

  7. #7
    Board Regular JenniferMurphy's Avatar
    Join Date
    Jul 2011
    Location
    Silicon Valley, CA, USA
    Posts
    1,132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    Quote Originally Posted by Joe4 View Post
    I am just trying to figure out if there is a way to avoid the loop altogether (i.e. if we could use something like the AVERAGE or SUM) function.
    But of course, we do not know if we can avoid the loop until we understand what kind of calculations are being done in the loop.
    The processing is nothing simple like that.

    Loops are very inefficient,
    Perhaps, but often necessary.

    so if you are truly concerned about "speeding things up" or "being more efficient", you want to avoid loops, if at all possible.
    I never said anything about either of those objectives.
    Using Office 2007 Pro on Win XP Pro

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    Quote Originally Posted by JenniferMurphy View Post
    Perfect. And I can get the value in each cell as text using:

    Thank you
    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,042
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Best way to skip 1st & last cell in range in UDF?

    That is true, they are sometimes necessary. But we have no way of knowing if they are or aren't, because you didn't post the details of the code, and what that may be doing.

    I never said anything about either of those objectives.
    You DID say:
    Is there a better way to do this?
    Quite frankly, your original code was fine, and did what you need. To be sure, Fluff's code is more concise, and eliminates some uneecessary steps. But while you may save a few lines of code, it probably won't make a huge difference in overall performance. However, if you were able to eliminate loops, that could make a big difference in overall performance. Now maybe you aren't able to, but the question bears asking.

    Sometimes people get so focused on little details that in the end may not make much of a difference, while missing bigger things that can make a big difference.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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