Returning a range of cells?

[Zerex71]

Group,

I'm sure it must be, but is it possible to use some function which
returns not the contents of the specified cells, but the cells
themselves, as a reference or input to another function? The reason I
ask is that I need to be able to operate on a range of cells, but I
only want to get cells back which contain a certain value.

For example, let's say I have a 1D (or 2D, it doesn't matter)
list/array of cells in Excel with the following values (it's for a golf
spreadsheet):

FRONT
FRONT
BACK
BACK
BACK
FRONT
BACK
FRONT
FRONT
FRONT
etc.

I want to run a function over those cells and return only the ones
which say FRONT or BACK (remember, I want the cells as a range, not the
values FRONT or BACK, I know how to get those). Then, with the range
returned, I want to select a field in a column either to the right or
left of this column. Basically, I am trying to pick out some data
contained in rows, but I only want the data for certain rows.

This might not make sense but one thing I am sure of is that I want a
list of cells returned to me to operate over, not their contents.

Mike

[Ken Johnson]

Hi Mike,
> This might not make sense but one thing I am sure of is that I want a
> list of cells returned to me to operate over, not their contents.

Just in case there is method in your madness, rather than madness in
your method, this UDF returns the address of the cells with the
requested value...

Public Function RangeAddress(Value As Variant, SearchRange As Range) As
String
Dim I As Integer
Dim J As Integer
Dim rngValueRange As Range
Dim blnFound As Boolean
Dim rngCell As Range
For Each rngCell In SearchRange.Cells
I = I + 1
If rngCell.Value = Value Then
Set rngValueRange = rngCell
blnFound = True
End If
If blnFound Then Exit For
Next rngCell
For Each rngCell In SearchRange.Cells
J = J + 1
If J <= I Then GoTo IGNORE
If rngCell.Value = Value Then
Set rngValueRange = Application.Union(rngValueRange, rngCell)
End If
IGNORE: Next rngCell
RangeAddress = rngValueRange.Address
End Function

If you are unfamiliar with UDFs then follow these steps...

1. Copy the code
2. With your Excel workbook either go Tools|Macro|Visual Basic Editor
OR Alt + F11 to get into the Visual Basic Editor

3. In the Visual Basic Editor go Insert|Module
4. Paste the code into the Module that appears
5. Either go File|"Close and Return to Microsoft Excel" OR Alt + F11 to
return to the normal Excel interface
6. Select the cell on your worksheet that is to show the address of the
range of cells with the chosen value ("FRONT" or "BACK") then use the
usual steps to insert a worksheetfunction. The function's name is
"RangeAddress", the first argument is the Value, "FRONT" or "BACK", and
the second argument is the range of cells you want searched for that
value.

Another option is the following function filled down an adjacent column
(I have assumed that the column with the "BACK" or "FRONT" are in
column A and that you are after a list of addresses of cells with
"FRONT"

=IF(A1="FRONT",ADDRESS(ROW(A1),COLUMN(A1)),"")

Ken Johnson

[Héctor Miguel]

hi, Mike !

> I'm sure it must be, but is it possible to use some function which returns not the contents of the specified cells
> but the cells themselves, as a reference or input to another function? The reason I ask is that
> I need to be able to operate on a range of cells, but I only want to get cells back which contain a certain value.

i'm not so sure what do you 'really need' to do with an 'offset' column [left/right] of 'certain' cells matching your criteria -?-
you mention the need of 'run a function OVER' the returned range/list/array of cells to 'pick up' some data
i guess you might want to 'include' in a UDF, the 'column-offset' [to operate with] so...

as Ken pointed you on how to write/copy-paste/use a user defined function [i'm only adding the column-offset variable]...

1) if you need 'only' the 'list of cells' as address...
Option Compare Text
Function OffsetAddress(SrcRange As Range, Criteria, _
Optional Col As Integer = 0) As String
Dim Cel As Range, NewRange As Range
For Each Cel In SrcRange
If Cel = Criteria Then _
If NewRange Is Nothing Then Set NewRange = Cel.Offset(, Col) _
Else Set NewRange = Union(NewRange, Cel.Offset(, Col))
Next
If Not NewRange Is Nothing Then _
OffsetAddress = NewRange.Address(0, 0): _
Set NewRange = Nothing
End Function

2) if you need a 'real' range/array of cells to be returned [to operate with]...
Option Compare Text
Function RangeOffset(SrcRange As Range, Criteria, _
Optional Col As Integer = 0) As Range
Dim Cel As Range
For Each Cel In SrcRange
If Cel = Criteria Then _
If RangeOffset Is Nothing Then Set RangeOffset = Cel.Offset(, Col) _
Else Set RangeOffset = Union(RangeOffset, Cel.Offset(, Col))
Next
End Function

this last function allows you to be used 'as argument' of 'other' worksheet-functions like:
- ****/counta, sum, max/min, average [-almost- any WF that accepts multiple ranges/arrays
-> BUT not in WF that 'requires' continuous range, like: rows, index, match [also the 'offset' WF] :-(

hth,
hector.

--- remaining post ---
> ... let's say I have a 1D (or 2D, it doesn't matter) list/array of cells in Excel with the following values (it's for a golf spreadsheet):
> FRONT
> FRONT
> BACK
> BACK
> BACK
> FRONT
> BACK
> FRONT
> FRONT
> FRONT
> etc.
>
> I want to run a function over those cells and return only the ones which say FRONT or BACK
> (remember, I want the cells as a range, not the values FRONT or BACK, I know how to get those).
> Then, with the range returned, I want to select a field in a column either to the right or left of this column.
> Basically, I am trying to pick out some data contained in rows, but I only want the data for certain rows.
> This might not make sense but one thing I am sure of is that I want a list of cells returned to me to operate over, not their contents.

[Zerex71]

Hi guys,

Thank you for the great response! I have not implemented either one
your solutions yet but let me elaborate a little more on what I have
done.

For starters, my cell data is actually something like this:

FRONT 71
BACK 65
BACK 64
FRONT 64
FRONT 65
FRONT 58
FRONT 61
FRONT 65
BACK 66
FRONT 63

What I was attempting to do (and still want to know how to do) is
extract a subtable if you will, or smaller range, of records which
contain rows keyed by either FRONT or BACK. That is, separate out the
records from the mix you see above into two different tables, and then
operate on the tables. (I don't actually need to put the data into the
spreadsheet as two separate tables -- I mean, extract in the sense of
return a vector or array of the rows for each, and then count the
frequency with which each score comes up). Does this make sense?

In other words, my overall problem is, I want to count how many times I
shot a 58 on the FRONT nine holes of a golf course, or how many times I
shot a 65 on the BACK nine. But I can't do this using conventional
Excel functions -- I looked up every reference/index function I could
and couldn't see a way to rig the inputs to those functions in such a
way as to return the correct batches of cells which would then feed
into a DCOUNT or COUNT function.

At any rate, I solved my problem for now using a PivotTable (my first
ever!). What I ended up doing was adding a leftmost column called
Match # ranging from 1-10, then created a PivotTable. That way the
PivotTable automatically separated out the scores into BACK/FRONT
columns and I could then just do a count in each column given the
particular score of interest. That worked perfectly for my purposes
but I am still curious as to how I can do the same thing without the
need for a PivotTable (because I am always guilty of using a more
complicated solution when a simpler one will do).

Thanks so much!

Mike

H=E9ctor Miguel wrote:
> hi, Mike !
>
> > I'm sure it must be, but is it possible to use some function which retu=
rns not the contents of the specified cells
> > but the cells themselves, as a reference or input to another function? =
The reason I ask is that
> > I need to be able to operate on a range of cells, but I only want to ge=
t cells back which contain a certain value.
>
> i'm not so sure what do you 'really need' to do with an 'offset' column [=
left/right] of 'certain' cells matching your criteria -?-
> you mention the need of 'run a function OVER' the returned range/list/arr=
ay of cells to 'pick up' some data
> i guess you might want to 'include' in a UDF, the 'column-offset' [to ope=
rate with] so...
>
> as Ken pointed you on how to write/copy-paste/use a user defined function=
[i'm only adding the column-offset variable]...
>
> 1) if you need 'only' the 'list of cells' as address...
> Option Compare Text
> Function OffsetAddress(SrcRange As Range, Criteria, _
> Optional Col As Integer =3D 0) As String
> Dim Cel As Range, NewRange As Range
> For Each Cel In SrcRange
> If Cel =3D Criteria Then _
> If NewRange Is Nothing Then Set NewRange =3D Cel.Offset(, Col) _
> Else Set NewRange =3D Union(NewRange, Cel.Offset(, Col))
> Next
> If Not NewRange Is Nothing Then _
> OffsetAddress =3D NewRange.Address(0, 0): _
> Set NewRange =3D Nothing
> End Function
>
> 2) if you need a 'real' range/array of cells to be returned [to operate w=
ith]...
> Option Compare Text
> Function RangeOffset(SrcRange As Range, Criteria, _
> Optional Col As Integer =3D 0) As Range
> Dim Cel As Range
> For Each Cel In SrcRange
> If Cel =3D Criteria Then _
> If RangeOffset Is Nothing Then Set RangeOffset =3D Cel.Offset(, Col=
) _
> Else Set RangeOffset =3D Union(RangeOffset, Cel.Offset(, Col))
> Next
> End Function
>
> this last function allows you to be used 'as argument' of 'other' workshe=
et-functions like:
> - ****/counta, sum, max/min, average [-almost- any WF that accepts multip=
le ranges/arrays
> -> BUT not in WF that 'requires' continuous range, like: rows, index, mat=
ch [also the 'offset' WF] :-(
>
> hth,
> hector.
>
> --- remaining post ---
> > ... let's say I have a 1D (or 2D, it doesn't matter) list/array of cell=
s in Excel with the following values (it's for a golf spreadsheet):
> > FRONT
> > FRONT
> > BACK
> > BACK
> > BACK
> > FRONT
> > BACK
> > FRONT
> > FRONT
> > FRONT
> > etc.
> >
> > I want to run a function over those cells and return only the ones whic=
h say FRONT or BACK
> > (remember, I want the cells as a range, not the values FRONT or BACK, I=
know how to get those).
> > Then, with the range returned, I want to select a field in a column eit=
her to the right or left of this column.
> > Basically, I am trying to pick out some data contained in rows, but I o=
nly want the data for certain rows.
> > This might not make sense but one thing I am sure of is that I want a l=
ist of cells returned to me to operate over, not their contents.

[Ken Johnson]

Hi Mike,

A very simple solution would be to use SUMPRODUCT.

The following assumes that the column with FRONT/BACK is column A
starting in A1 and finishing in A100 (You've probably got a heading
though, so change to suit) and the scores are in column B...

=SUMPRODUCT(($A$1:$A$100=A1)*($B$1:$B$100=B1))

fill this formula down to return the frequency of each score with
respect to FRONT/BACK.

Ken Johnson