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

[JenniferMurphy]

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

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Group[/TD]
[TD="align: center"]Rating[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]North[/TD]
[TD="align: center"]25.2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]South[/TD]
[TD="align: center"]14.7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]East[/TD]
[TD="align: center"]52.0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]West[/TD]
[TD="align: center"]33.8[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Average[/TD]
[TD="align: center"]31.4[/TD]
[/TR]
</tbody>[/TABLE]

I call the UDF from another cell like this:

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

=Tally(B1:B6)

But I want the UDF to skip over these boundary cells. Here's my code, which works.

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]

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]

How about

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]

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]

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.

 

[JenniferMurphy]

How about

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:

Ratings(i).Text

Thank you

 

[JenniferMurphy]

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.

 

[Fluff]

Perfect. And I can get the value in each cell as text using:

Thank you

You're welcome & thanks for the feedback

 

[Joe4]

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.