One way:

Option Explicit
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

If Application.CountIf(range_look, find_it) < occurrence Then
Nth_Occurrence = ""
Exit Function
End If

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

But I think that there's a problem if the first item in the range matches the
find_it string.

I think I'd replace:
Set rFound = range_look.Cells(1, 1)
with
Set rFound = range_look.Cells(range_look.Cells.Count)

And I like to specify all the parms in the .find command--and I like to see the
keywords, too:

Option Explicit
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

If Application.CountIf(range_look, find_it) < occurrence Then
Nth_Occurrence = ""
Exit Function
End If

'removed
'Set rFound = range_look.Cells(1, 1)

Set rFound = range_look.Cells(range_look.Cells.Count)

For lCount = 1 To occurrence
Set rFound = range_look.Find(what:=find_it, _
after:=rFound, LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)

End Function



tomandrobyn wrote:

I've tried using the Nth_Occurence function (below) from the Ozgrid
site. The problem is when I set it up for 1,2,3,4,5 occurences and if
there is only one occurence it shows that one in all 5 cells. Could a
slight change in this code return a blank if looking for a 2nd
occurnace and there is no 2nd?
Thanks in advance,
Tom

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

--
tomandrobyn
------------------------------------------------------------------------
tomandrobyn's Profile: http://www.excelforum.com/member.php...o&userid=13879
View this thread: http://www.excelforum.com/showthread...hreadid=488265

--

Dave Peterson