help in fixing function that finds nth occurence of string

[javon27]

This is the function I found to solve my problem. Unfortunately, it's not working exactly as it should. For one, whenever I try to reference a worksheet in another workbook, it gives me a #Value error. In the meantime, I copied the sheet into the same workbook and it works somewhat...

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 = 0 To occurrence
            Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
        Next lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)

End Function

I use the function as such:

=Nth_Occurrence('Sheet1'!B4:F4,"YES",0,-(ROW()-2),0)

The "-(Row()-2)" part is because I can't figure out to rewrite this function to accept absolute references for the row.

The main problem I'm having is that no matter how many times it finds the string "YES", or even if it doesnt, it always returns the value at "Offset(-(Row()-2),0)" which happens to be the date "8/25/10" for that column.

The second thing is getting rid of the need to find the offset cell, and instead just use an absolute row for the cell in the same column (in this case row 2).

The third thing is the field for Occurence starts counting at zero (0). So in essence if I'm looking for the first occurence, I have to tell it that it is the 0th occurence, which my users might not know to do that.

If anyone can help, I would greatly appreciate it.