Excel Mac - User defined function

[birchs92]

Dear all, the following UDF is working on Windows (Excel 2007) but results in an '#Value!' on MacOS (Excel Mac 2011), I have tried various suggestions found on the internet, but I am out of options !!

Any suggestions would be appropriated. Many Thanks in advance. Simon

Excel: Find the nth Occurrence of a Value. Find nth Occurrence in Excel

Public 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

 

[Andrew Poulsom]

It seems that you can't use Find in a UDF on Excel 2011.

Mac Excel 2011 VBA UDF not working - SET or FIND command seems to be the issue - Stack Overflow

 

[birchs92]

Thanks for the response....I have read link and are not clear on how to modify UDF

Any suggestion would be appreciated

Many Thanks
Simon

 

[Andrew Poulsom]

Can't you use an array formula?

 

[birchs92]

Thanks, try to find an example now