F
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
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