Need help on a UDF (Find n occurrence)

[vietdieu]

Hi experts,
I have one of the “Find n occurrence” UDF long time ago (see below), this function will find the position of the n occurrence of a number from a range then offset it. This work fine but trouble when the range that it look for doesn’t count the cell with different format then return error, you can find it very easy when you open my attachment. Can you help me tweak this UDF a bit so it can neglect the format. Thank you so much for help.

Link

-----------

Function FINDNTHOCC(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

FINDNTHOCC = rFound.Offset(offset_row, offset_col)

End Function

 

[vietdieu]

Please anybody!!

 

[StephenCrump]

Change your Lookin: argument from xlValues to xlFormulas.

 

[vietdieu]

It works just by that :ddddd
many thanks!!!!!!!!!!!!!!

 

[vietdieu]

I need another little help here:

It work good but there was another problem but:

This function will not auto-recalculate. That means it finds good for the first time but if the cell changes values then it will not recalculate, it keeps the same value. But if I close the workbook and open it back then it will find the right one. How can we make this auto-calculate just like any of the Excel function (sum,subtract...)

Thanks again!

 

[StephenCrump]

The function should update automatically if any value changes in the range: range_look, because you pass this range to the function as an argument. (Unless you've set Excel calculation to manual).

But it won't update if rFound.Offset(offset_row, offset_col) lies outside range_look.

To fix this, insert this line after your Dim statements:

Application.Volatile True

And you also need to change the next line:

Set rFound = range_look.Cells(1, 1)

 

[StephenCrump]

And you also need to change the next line:

Set rFound = range_look.Cells(1, 1)

Sorry, please ignore this last comment.

Your code was correct to start with, but somehow I messed up my copy.

 

[vietdieu]

It works, thanks so much. But now it has another issue. It doesn't work (either error or give the wrong value) on a range with formulas or range in a table and one more thing is: If I ask it to find the n occurrence which actually there are less than n occurrence (let's say there are only (n-2) occurrence) but it still give me the (n-2) value.

Please see the file

Many thanks to make this UDF work properly!!

 

[StephenCrump]

Try this:

Function FINDNTHOCC(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long) As Variant

    Dim lCount As Long
    Dim rFound As Range, rFound1 As Range
    Dim bSuccess As Boolean
    Application.Volatile

    Set rFound1 = range_look.Find(find_it, range_look(range_look.Rows.Count, range_look.Columns.Count), xlValues, xlWhole)
    If rFound1 Is Nothing Then GoTo EndFunction
    Set rFound = rFound1
    bSuccess = True
    
    For lCount = 2 To occurrence
        Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
        If rFound.Address = rFound1.Address Then
            bSuccess = False
            Exit For
        End If
    Next lCount

EndFunction:
    If bSuccess Then
        FINDNTHOCC = rFound.Offset(offset_row, offset_col)
    Else
        FINDNTHOCC = "Occurrence " & occurrence & " doesn't exist!"
    End If
    
End Function

At the moment, you're searching only single columns. If you want to search multiple columns you'll need to set the SearchOrder argument, i.e. to make it clear whether the nth find is xlByColumns or xlByRows.