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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,138
I have a little UDF that does some tallies on a column of cells (B2:B5).

R/CAB
1GroupRating
2North25.2
3South14.7
4East52.0
5West33.8
6Average31.4

<tbody>
</tbody>

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
Office Version
365
Platform
Windows
How about
Rich (BB 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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,138
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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:

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,138
How about
Rich (BB 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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,138
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top