Nth Occurance Function

[stapuff]

Below is a function that works with a formula to return the Nth occurance. It is not returning the correct data. Just appears like the function will not fail.

in sheet Champs
L1 formula is =Nth_Occurrence(N_TH,Champs!$C$3,1,0,4)
L2 formula is =Nth_Occurrence(N_TH,Champs!$C$3,2,0,4)
L3 formula is =Nth_Occurrence(N_TH,Champs!$C$3,3,0,4)
L4 formula is =Nth_Occurrence(N_TH,Champs!$C$3,4,0,4)
L5 formula is =Nth_Occurrence(N_TH,Champs!$C$3,5,0,4)
L6 formula is =Nth_Occurrence(N_TH,Champs!$C$3,6,0,4)
L7 formula is =Nth_Occurrence(N_TH,Champs!$C$3,7,0,4)

The first formula is broken down like this
N_TH is a named range of sheet1 $B$2:$B$20
Champs!$C$3 is what I want to find the number of occurances of
C3 = "CH"
1 = occurance #
0 = row.offset
4 = column.offset


Example -
In sheet1 $B$2:$B$20 there are 3 occurences of "CH"

B7 = Lima
B8 = Denver
B9 = Oakland

Here are the results I get

L1 Lima
L2 Denver
L3 Oakland
L4 Lima
L5 Denver
L6 Oakland
L7 Lima

Cells L4-L7 should fail and return an error but it just starts over

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

 

[ChrisOswald]

Hi,

Try this fix to the UDF

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 bottomCount                 As Long
    Dim rFound                      As Range
    Dim rFirst                      As Range
 
    'see if first cell in range contains value looking for
    'if so, set rFirst to that value, otherwise use find method
    If range_look.Cells(1, 1) = find_it Then
        Set rFirst = range_look.Cells(1, 1)
    Else
        Set rFirst = range_look.Find(find_it, range_look.Cells(1, 1), xlValues, xlWhole)
    End If
    'if rFirst is nothing, then value is nowhere in range.
    If rFirst Is Nothing Then
        Nth_Occurrence = CVErr(xlErrNA)
        Exit Function
    End If
    Set rFound = rFirst
    'counting from 2 because rFirst is the first occurrance
    For lCount = 2 To occurrence
        Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
 
        'if the ranges are the same, we know we "wrapped" around to the
        'beginning of the range and are now double searching
        If rFound.Address = rFirst.Address Then
            Nth_Occurrence = CVErr(xlErrNA)
            Exit Function
        End If
    Next lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

 

[stapuff]

Chris -

Work very well. It returns error's where it should. Can this UDF return "" when an error occurs?

Anyways - I appreciate your suggestions.

Stapuff

 

[Haseeb Avarakkan]

How about to using a formula?

A2, Control+Shift+Enter, not just Enter, copy down...

=INDEX($F$2:$F$20,SMALL(IF(N_TH=Champs!$C$3,ROW($F$2:$F$20)-ROW($F$2)+1),ROWS(A$2:A2)))

 

[ChrisOswald]

StaPuff;

to have the UDF return "", just replace the lines that have

Nth_Occurrence = CVErr(xlErrNA)

with

Nth_Occurrence = ""

If this is for a production book, I'd suggest using an array formula like Haseeb provided because array formulas almost always are faster and take less memory than UDF's. (Actually, an array formula was my first thought, but I really haven't been doing enough formula work the past few months, so...)

If you're playing around with some numbers, the UDF is possibly better just because there's a lot less to type