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),"")
=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),"")
Code:
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