Test if CELL is in RANGE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)?
 
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
 
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.
 
Thanks Gazza's, this will do very nicely.

Gary''s Student said:
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.
Click to expand...
 
Kind of long but here it is in one formula:

A1 = cell address to check

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

Biff
 
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?
 
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"...
;-]
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
You are welcome !

--
Regards,
Luc.

"Festina Lente"


Roger Govier said:
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 said:
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 said:
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


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

"Festina Lente"


:

Kind of long but here it is in one formula:

A1 = cell address to check

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

Biff

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)?
Click to expand...
Click to expand...
Click to expand...
 
I love it! Thanks Papa

PapaDos said:
You are welcome !

--
Regards,
Luc.

"Festina Lente"


Roger Govier said:
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 said:
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"


:

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


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

"Festina Lente"


:

Kind of long but here it is in one formula:

A1 = cell address to check

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

Biff

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)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top