Test if CELL is in RANGE

[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)?

 

[Guest]

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

 

[Guest]

Thanks Gary's

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

 

[Guest]

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.

 

[Guest]

Thanks Gazza's, this will do very nicely.

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.

 

[Biff]

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

 

[Guest]

=NOT( ISERR( range INDIRECT( A1 ) ) )

 

[Biff]

That's really slick!

Biff

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

"Festina Lente"

 

[Roger Govier]

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?

 

[Guest]

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"...
;-]

 

[Roger Govier]

Thank you Luc

I was aware of its use where B11 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

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?

 

[Guest]

You are welcome !

--
Regards,
Luc.

"Festina Lente"

Thank you Luc

I was aware of its use where B11 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

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)?

 

[Guest]

I love it! Thanks Papa

You are welcome !

--
Regards,
Luc.

"Festina Lente"

Thank you Luc

I was aware of its use where B11 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

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)?