Nth_Occurence User Defined Formula

[tomandrobyn]

I've tried using the Nth_Occurence function (below) from the Ozgrid
site. The problem is when I set it up for 1,2,3,4,5 occurences and if
there is only one occurence it shows that one in all 5 cells. Could a
slight change in this code return a blank if looking for a 2nd
occurnace and there is no 2nd?
Thanks in advance,
Tom

Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range

Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues,
xlWhole)
Next lCount
Nth_Occurrence = rFound.offset(offset_row, offset_col)
End Function

 

[Leith Ross]

Hello Tomandrobyn,

The problem is the loop doesn't detect when you have started reading
through the range again. I made som changes to detect when the loop has
start reading through the range again.

Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Dim rStart As Range

Set rFound = range_look.Cells(1, 1)
rStart = rFound

For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
if rFound.Address = rStart.Address And lCount > 1 Then Exit For
Next lCount
Nth_Occurrence = rFound.offset(offset_row, offset_col)
End Function

Sincerely,
Leith Ross

 

[Dave Peterson]

One way:

Option Explicit
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)

Dim lCount As Long
Dim rFound As Range

If Application.CountIf(range_look, find_it) < occurrence Then
Nth_Occurrence = ""
Exit Function
End If

Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)

End Function

But I think that there's a problem if the first item in the range matches the
find_it string.

I think I'd replace:
Set rFound = range_look.Cells(1, 1)
with
Set rFound = range_look.Cells(range_look.Cells.Count)

And I like to specify all the parms in the .find command--and I like to see the
keywords, too:

Option Explicit
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)

Dim lCount As Long
Dim rFound As Range

If Application.CountIf(range_look, find_it) < occurrence Then
Nth_Occurrence = ""
Exit Function
End If

'removed
'Set rFound = range_look.Cells(1, 1)

Set rFound = range_look.Cells(range_look.Cells.Count)

For lCount = 1 To occurrence
Set rFound = range_look.Find(what:=find_it, _
after:=rFound, LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)

End Function

 

[tomandrobyn]

Dave, thanks for the code, that works perfect!

Leith, thanks for replying but I did have a problem with the code..
the formulas turned to VALUE errors?

Thanks again guys, you're life savers