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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. 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
55,245
Office Version
  1. 365
Platform
  1. 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,438
Office Version
  1. 365
Platform
  1. 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?
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
56,492
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,438
Office Version
  1. 365
Platform
  1. 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

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,438
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
56,492
Office Version
  1. 365
Platform
  1. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,977
Messages
5,621,935
Members
415,867
Latest member
mauroccs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top