Ok It works , just needed the True argument and not the fasle in the
indirect when using a sting in an array. THANK YOU !


wrote:
Gary's student,

Thanks for the resoponse, I do not want the value; I want to use the
result of the function as range address in a array equation. I want to
use the range returned in this equation
{=SUM(LEN(Results!$C$2:C$10)-LEN(SUBSTITUTE(Results!$C$2:C$10,A4,"")))/LEN(A4)}
I need the equation to be dynamic. I will try putting it directly in
the equation with the Dim change.

{=SUM(LEN("Results!"&(Nth_Occurrence(Results!$A$1: $A$109,B$1,1,2,0))&":"&(Nth_Occurrence(Results!$A$ 1:$A$109,B$1,COUNTIF(Results!$A$2:$A$109,B$1),0,0) ))-LEN(SUBSTITUTE("Results!"&(Nth_Occurrence(Results! $A$1:$A$109,B$1,1,2,0))&":"&(Nth_Occurrence(Result s!$A$1:$A$109,B$1,COUNTIF(Results!$A$2:$A$109,B$1) ,0,0)),A4,"")))/LEN(A4)}

It just will not pass the range into the array equation.

Anyone have any other thoughts.

The reason I am doing this is I have multiple occurences of a word in
several cells over a multicell range, I need to count them and the
words are listed in each cell in a list ex. cell C1 has Adjuster Email,
Adjuster Fax, Emp. Name, Emp. Address, and cell C2 has Adjuster Email,
Emp. Name, Emp. Address, I need to group and count the occurence of
each word.

Thanks again.

Gary''s Student wrote:
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