OK tough one !

[Fuchsrr]

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

 

[Guest]

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.

 

[Harlan Grove]

(e-mail address removed) 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: ....
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. ....
="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))

....

Note that the first two references to B1 are row-absolute, B$1, but the
third is fully relative, B1. Intentional? Also, the row and column
offset values differ in the two calls to your udf.

From your prior description, it looks like you want the range

corresponding to the rows with the first and last instance of B1 in
Results!A1:A109 but two columns to the right, so in column C. If so,
that range would be given by the array formula

=INDEX(Results!$1:$65536,MIN(ROW(List))-1
+MATCH(B$1,Results!$A$1:$A$109,0),2):
INDEX(Results!$1:$65536,MIN(ROW(List))-1
+MATCH(2,1/(B$1=Results!$A$1:$A$109)),2)

Hardcoding the result column allows for a slightly shorter array
formula

=INDEX(Results!$C:$C,MIN(ROW(List))-1+MATCH(B$1,Results!$A$1:$A$109,0),2):
INDEX(Results!$C:$C,MIN(ROW(List))-1+MATCH(2,1/(B$1=Results!$A$1:$A$109)),2)

Both are range references, so could be used as terms in longer array
formulas.

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.

....

Your formula just produces a string for the same reason ="A1" returns
the string "A1" rather than the value of cell A1. If you want to
convert it to a range reference, you need to pass it as an argument to
the INDIRECT function. However, calling a udf twice inside a volatile
function call will produce slow recalculation.

 

[Fuchsrr]

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(Results!$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.

 

[Fuchsrr]

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