Test if CELL is in RANGE
 

Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Sub is_it_in()
Dim r As Range
Set r = Range("print_area")
If Intersect(ActiveCell, r) Is Nothing Then
MsgBox ("its not in there")
Else
MsgBox ("its in there")
End If

End Sub

select the cell and run the macro
--
Gary's Student


"Vaughan" wrote:

Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Thanks Gary's

Is there any way of getting the same result with a worksheet formula?

"Gary''s Student" wrote:

Sub is_it_in()
Dim r As Range
Set r = Range("print_area")
If Intersect(ActiveCell, r) Is Nothing Then
MsgBox ("its not in there")
Else
MsgBox ("its in there")
End If

End Sub

select the cell and run the macro
--
Gary's Student


"Vaughan" wrote:

Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Easy, but longer. In A2 thru A8 enter:

=ROW(print_area)
=A2+ROWS(print_area)
=COLUMN(print_area)
=A4+COLUMNS(print_area)
=ROW(INDIRECT(A1))
=COLUMN(INDIRECT(A1))
=IF((A6=A2)*(A6<=A3)*(A7=A4)*(A7<=A5),"in","out" )

and then in cell A1 enter the address of the cell, say Z100.


Note that this will only work for nice rectangular ranges.
--
Gary's Student


"Vaughan" wrote:

Thanks Gary's

Is there any way of getting the same result with a worksheet formula?

"Gary''s Student" wrote:

Sub is_it_in()
Dim r As Range
Set r = Range("print_area")
If Intersect(ActiveCell, r) Is Nothing Then
MsgBox ("its not in there")
Else
MsgBox ("its in there")
End If

End Sub

select the cell and run the macro
--
Gary's Student


"Vaughan" wrote:

Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Thanks Gazza's, this will do very nicely.

"Gary''s Student" wrote:

Easy, but longer. In A2 thru A8 enter:

=ROW(print_area)
=A2+ROWS(print_area)
=COLUMN(print_area)
=A4+COLUMNS(print_area)
=ROW(INDIRECT(A1))
=COLUMN(INDIRECT(A1))
=IF((A6=A2)*(A6<=A3)*(A7=A4)*(A7<=A5),"in","out" )

and then in cell A1 enter the address of the cell, say Z100.


Note that this will only work for nice rectangular ranges.
--
Gary's Student


"Vaughan" wrote:

Thanks Gary's

Is there any way of getting the same result with a worksheet formula?

"Gary''s Student" wrote:

Sub is_it_in()
Dim r As Range
Set r = Range("print_area")
If Intersect(ActiveCell, r) Is Nothing Then
MsgBox ("its not in there")
Else
MsgBox ("its in there")
End If

End Sub

select the cell and run the macro
--
Gary's Student


"Vaughan" wrote:

Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

That's really slick!

Biff

"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Hi Luc

That is indeed a very slick answer, as Biff has stated, and works
perfectly with a test I have just run.
However, I am unfamiliar with the construct
(range INDIRECT(A1))

Could you enlighten me more on this aspect please?

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or
more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

The "space" is just one of the 3 "reference operators", along with "," and ":".
It is the "Intersection operator".
It is not used much by most Excel users.
Just take a look in Excel's help for "calculation operators"...
;-]
--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Hi Luc

That is indeed a very slick answer, as Biff has stated, and works
perfectly with a test I have just run.
However, I am unfamiliar with the construct
(range INDIRECT(A1))

Could you enlighten me more on this aspect please?

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or
more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

Thank you Luc

I was aware of its use where B1:D1 contains 2004, 2005, 2006 and A2:A5
contains North, East, South and West,
entering =2005 South would return the value in cell C4
but I had never thought about its use as returning the intersection when
used within a formula, as you did.

Thank you for bringing this to my attention.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
The "space" is just one of the 3 "reference operators", along with ","
and ":".
It is the "Intersection operator".
It is not used much by most Excel users.
Just take a look in Excel's help for "calculation operators"...
;-]
--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Hi Luc

That is indeed a very slick answer, as Biff has stated, and works
perfectly with a test I have just run.
However, I am unfamiliar with the construct
(range INDIRECT(A1))

Could you enlighten me more on this aspect please?

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or
more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

You are welcome !

--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Thank you Luc

I was aware of its use where B1:D1 contains 2004, 2005, 2006 and A2:A5
contains North, East, South and West,
entering =2005 South would return the value in cell C4
but I had never thought about its use as returning the intersection when
used within a formula, as you did.

Thank you for bringing this to my attention.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
The "space" is just one of the 3 "reference operators", along with ","
and ":".
It is the "Intersection operator".
It is not used much by most Excel users.
Just take a look in Excel's help for "calculation operators"...
;-]
--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Hi Luc

That is indeed a very slick answer, as Biff has stated, and works
perfectly with a test I have just run.
However, I am unfamiliar with the construct
(range INDIRECT(A1))

Could you enlighten me more on this aspect please?

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or
more
specifically if it falls within the 'print_area' named range)?

Test if CELL is in RANGE
 

I love it! Thanks Papa

"PapaDos" wrote:

You are welcome !

--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Thank you Luc

I was aware of its use where B1:D1 contains 2004, 2005, 2006 and A2:A5
contains North, East, South and West,
entering =2005 South would return the value in cell C4
but I had never thought about its use as returning the intersection when
used within a formula, as you did.

Thank you for bringing this to my attention.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
The "space" is just one of the 3 "reference operators", along with ","
and ":".
It is the "Intersection operator".
It is not used much by most Excel users.
Just take a look in Excel's help for "calculation operators"...
;-]
--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Hi Luc

That is indeed a very slick answer, as Biff has stated, and works
perfectly with a test I have just run.
However, I am unfamiliar with the construct
(range INDIRECT(A1))

Could you enlighten me more on this aspect please?

--
Regards

Roger Govier


"PapaDos" wrote in message
...
=NOT( ISERR( range INDIRECT( A1 ) ) )
--
Regards,
Luc.

"Festina Lente"


"Biff" wrote:

Kind of long but here it is in one formula:

A1 = cell address to check

=AND(COLUMN(INDIRECT(A1))=COLUMN(range),COLUMN(IN DIRECT(A1))<=COLUMN(range)+COLUMNS(range)-1,ROW(INDIRECT(A1))=ROW(range),ROW(INDIRECT(A1))< =ROW(range)+ROWS(range)-1)

Biff

"Vaughan" wrote in message
...
Is it possible to test if a given cell falls within a range (or
more
specifically if it falls within the 'print_area' named range)?