Custom Function: Nth Occurrence, not returning a result

[Lizabeta]

I had some success with this in another workbook. Though I was having a problem. I started making a 'generic' workbook for here, added the custom function and updated my formulas. Now it won't return any values. It just gives me 0:00 instead of returning the time.

My original problem was that instead of returning only a 2nd occurrence if there was one... if there was NO 2nd occurrence, it would return the 1st occurrence.

Function Nth(find_it As String, range_look As Range, 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

and my formula:
=Nth($A1,'Routing Times'!$A$1:$C$10,2,0,2)

=Nth(find_it,range_look,occurrence,offset_row,offset_col)

So it should be:
=Nth(Jane Doe, on the routing sheet range, 2nd occurrence, no offset row, 2nd column from reference)

This is returning 0:00 in my 'generic test' workbook when it should return the time listed.
In my original workbook, it's returning the 2nd occurrence if there is one, but otherwise the 1st occurrence. I only want it to return a 2nd occurrence if there is one, otherwise nothing.

I'm not seeing a place to attach my 'generic' workbook... if someone could point out how, I'd be happy to attach my bitty bitty workbook.

 

[Ron Coderre]

Can you work with something like this?:

Function Nth(find_it As String, range_look As Range, occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim cCell As Range
lCount = 0
On Error GoTo errTrap
For Each cCell In range_look.Resize(ColumnSize:=1)
    If cCell.Value = find_it Then
        lCount = lCount + 1
        If lCount = occurrence Then
            Nth = cCell.Offset(RowOffset:=offset_row, ColumnOffset:=offset_col)
            Exit For
        End If
    End If
Next cCell
errTrap:
If lCount <> occurrence Then
    Nth = "No match"
End If
End Function

 

[Lizabeta]

Its giving me a "No Match" for one I know has several matches... Let me play with it a bit and see if I can get it to work.