Modify a custom function

[brenton]

hi, need help modifying this custom function. I need to find the nth occurrence of a word and when there isn't an nth occurrence for it to return no value. Right now this function will keep cycling through and delivering the same values. If there are 3 occurrences of the word "yes", when i ask for the 4th occurence it will give me the first one back. How do i stop it from repeating at the beginning.
=Nth_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)
Formula in cell =IFERROR(Nth_Occurrence($M$124:$M$149,"Yes",1,0,-7),"")

Function Nth_Occurrence(range_look As Range, find_it As String, _occurrence As Long, offset_row As Long, offset_col As Long)
'http://www.ozgrid.com/Excel/find-nth.htm
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

 

[JLGWhiz]

At just a quick glance, you have a variable declared in the user provided values as _occurrence but in the body of the fuction the underscore is missing. I am surprised that it does not yield a runtime error.

 

[brenton]

At just a quick glance, you have a variable declared in the user provided values as _occurrence but in the body of the fuction the underscore is missing. I am surprised that it does not yield a runtime error.

Thanks for pointing that out, the underscore got move when i pasted from excel to this forum. it is only there to allow the first line of code to extend to the second line. Unfortunately that is not the problem. The code runs, it just needs to be modified so it doesnt repeat if the "nth" value doesnt exist. I have a column that contains "yes" if a condition exists. If yes appears three times in that column and i ask for the 5th "yes" it will cycle back through and give me the value for the 2nd yes.

[COLOR=#333333]Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)[/COLOR]
'http://www.ozgrid.com/Excel/find-nth.htmDim 
lCount As LongDim 
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) 
[COLOR=#333333]End Function[/COLOR]

 

[p45cal]

There is more than just it looping round the occurrences at fault with this function; if what is sought is in the first cell of the range being searched it won't count it as the first instance!
Try:

Function Nth_Occurrence(range_look As Range, find_it As String, occurrence As Long, offset_row As Long, offset_col As Long)
Set rFound = range_look.Find(what:=find_it, after:=range_look.Cells(range_look.Cells.Count), LookIn:=xlValues, lookat:=xlWhole)
If rFound Is Nothing Then
  Nth_Occurrence = "Not found"
Else
  FirstFound = rFound.Address
  Do
    lCount = lCount + 1
    If lCount = occurrence Then Exit Do
    Set rFound = range_look.Find(what:=find_it, after:=rFound, LookIn:=xlValues, lookat:=xlWhole)
  Loop Until rFound.Address = FirstFound
  If lCount = occurrence Then
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)
  Else
    Nth_Occurrence = "only " & lCount & " occurrences exist"
  End If
End If
End Function

There is a little problem with both the original and this function though: If you use any but 0 and 0 for the two offsets, if you change what's in those offset cells, the formula will not update automatically. It will update if you change one of the cells being searched however, or by forcing a recalculation of the sheet, say by Ctrl+Alt+F9.

 

[brenton]

Thanks p45cal

That updating is kind of an issue i had not even realized, but your solution does get it going, maybe i can just recalculate with vba.