| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| Cell References | Excel Discussion (Misc queries) | |||
| Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
| Cell address in a range | Excel Discussion (Misc queries) | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Hybrid Mode
