First Dim the function as String.

Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long) as String

Then if the function call in in cell Z100, INDIRECT(Z100) should get the
value of the cell whose address is returned by the function.

--
Gary''s Student


" wrote:

I am trying to make dynamic ranges that change as a list changes the
ranges fall in parts of the list. I use this custome function:

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(0, 2).Address

End Function

This will return the address of the cell 2 colums over from the nth
occurence if the item in the list.

I use two of this custome function joined with the sheet name to get a
range.

This is my equation
="Results!"&(Nth_Occurrence(Results!$A$1:$A$109,B$ 1,1,2,0))&":"&(Nth_Occurrence(Results!$A$1:$A$109, B$1,COUNTIF(Results!A2:A121,B1),0,0))

Results A1:A109 is the name of the worksheet the list is on and the
list range

B$1 references the item I am looking for in the list it is a name

The list is a list of names and products that they sell

Ex.
cola colb
1 bob fruit
2 bob caned goods
3 bob candy
4 jeff fruit
5 jeff candy
6 jeff canned goods

My result is _____Results!$C$2:$C$10_____


This looks good but I can't get any other equations to use this as a
range . The equation works great I just can not use the address
returned as a range in any other equations.

How to I get other equation to recognize the result as a valid range?

Thanks,

Rob