See if cell is in Range

Thanks for taking the time to read my question.

I'm passing a string that is a cell reference to a function. In that 
function I want to determine if that cell reference is within a predetermined 
range. Not sure how to do that. Right now I have:

Function CheckRange(TheSheet As String, TheCell As Range) As Boolean

If TheSheet = "Sheet1" Then
    if TheCell In Range("B4:B30") then 'This line is red as it is incorrect
        CheckRange = True
    End If

What do I use instead of "In"?

Thanks,

Brad
0
Utf
5/13/2010 6:44:11 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
819 Views

Similar Articles

[PageSpeed] 23

Brad
    Use something like:
If Intersect(ThisRange, ThatRange) is Nothing Then
This says if ThisRange is NOT a part of ThatRange.  If you want the 
opposite, stick a Not in front of Intersect.  HTH  Otto

"Brad" <Brad@discussions.microsoft.com> wrote in message 
news:69544E6A-F58D-41EF-9514-144A773A6FF9@microsoft.com...
> Thanks for taking the time to read my question.
>
> I'm passing a string that is a cell reference to a function. In that
> function I want to determine if that cell reference is within a 
> predetermined
> range. Not sure how to do that. Right now I have:
>
> Function CheckRange(TheSheet As String, TheCell As Range) As Boolean
>
> If TheSheet = "Sheet1" Then
>    if TheCell In Range("B4:B30") then 'This line is red as it is incorrect
>        CheckRange = True
>    End If
>
> What do I use instead of "In"?
>
> Thanks,
>
> Brad 

0
Otto
5/13/2010 7:00:28 PM
Found Answer Here

http://www.exceltip.com/st/Determine_if_a_cell_is_within_a_range_using_VBA_in_Microsoft_Excel/484.html


"Brad" wrote:

> Thanks for taking the time to read my question.
> 
> I'm passing a string that is a cell reference to a function. In that 
> function I want to determine if that cell reference is within a predetermined 
> range. Not sure how to do that. Right now I have:
> 
> Function CheckRange(TheSheet As String, TheCell As Range) As Boolean
> 
> If TheSheet = "Sheet1" Then
>     if TheCell In Range("B4:B30") then 'This line is red as it is incorrect
>         CheckRange = True
>     End If
> 
> What do I use instead of "In"?
> 
> Thanks,
> 
> Brad
0
Utf
5/13/2010 7:07:01 PM
Reply:

Similar Artilces:

Highlighting Cells #2
In columns A:F, the sheet is text. I would like to highlight a row in a color, (Say A5:F:5) when an "A" is typed into F:5 Can this be done? -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27292 View this thread: http://www.excelforum.com/showthread.php?threadid=470747 Your question is confusing so I don't quite understand what you want to do. It sounds like you want conditional formatting. Here is what I think you want to do. If not please be ...

copying data to an adjacent cell
I have a huge list of products in Excel which have associated images. I have changed all the image file names to reflect the product ID no for example: product named abc and image file name abc.jpg. Is it possible to configure excel to automatically attach the> .jpg file extension to cells that have been copied and pasted from the product ID no. I WANT TO COPY A CELL WITH ABC AND PASTE ABC.jpg At the moment I am keying in the file extension .jpg and I have a few thousand products to do. If A1 contains ABC put =A1&".jpg" in cell B1 and copy on down -- Gary''s S...

checkbox whithin a cell
Is there a way to get a checkbox within a cell. The forms toolbar only lets you make a floating one. I want one directly in a cell. Hi AFAIK not possible -- Regards Frank Kabel Frankfurt, Germany Greg wrote: > Is there a way to get a checkbox within a cell. > The forms toolbar only lets you make a floating one. > I want one directly in a cell. Maybe you could just use a character. =char(252) formatted with wingdings might be ok??? or =char(254) Greg wrote: > > Is there a way to get a checkbox within a cell. > The forms toolbar only lets you make a floating one. &g...

delete drop-down box in cell
I have a problem removing a drop-down list box in a cell and have trie using clear contents, delete cell, the delete button on the keyboard Can someone please tell me how to remove this box from the cell. Thank -- Message posted from http://www.ExcelForum.com Hi might be one created by data / validation ... click on the cell choose data / validation choose Clear All regards JulieD "brain77 >" <<brain77.1apguz@excelforum-nospam.com> wrote in message news:brain77.1apguz@excelforum-nospam.com... > I have a problem removing a drop-down list box in a cell and have tr...

Question about cell assignment
I am making a spreadsheet where I add one new data point to a colum each day (e.g. number of cars I honked at yesterday) I also have a box that calculates statistics based on the data entere (e.g. total number of cars, percent increase over time, etc) How can I assign a formula in the "statistics box" that will updat when I enter in the new value in the "data column"? Is there som general notatation that will tell the cell to search for the mos recent value in a column of data? Thanks for your comments -- Message posted from http://www.ExcelForum.com One formula woul...

Selecting evenly spaced cells
Hello, I have data set up such that I need to sum the value of a certain cell and all its offsets i.e. I have data in B2, B12, B22, B32, B42 and I want to sum up only these values, however I don't want to explicitly state each cell. For my other summation I want to sum up B3, B13, B23, B33, B43 and etc. What would this group suggest be the most elegant way in doing so Let me try to further explain My worksheet has this sort of setup Summary A's - Summed Up Value B's - Summed Up Value C's - Summed Up Value Issue 1 A - 5 B - 4 C - 2 Issue 2 A - 10 B - 5 C - 9 Issue 3 A...

extracting numbers from other cells (search)
Hi All Is there a way in excel to create a search cell?? ie if i have a column with various numbers ie. each cell containing numbers with comma like 12,2,7,34,5,21 etc (say 10 cells in a coulumn with various numbers separated by commas) is there a way of extracting number from the cells which i enter as a search, if i wanted to search 1,2,3,4,5,6 from the cells is there a way excel can show me which cells contain these numbers????? my newsreader crashed just after I hit the send button--sorry if this duplicates. I'd use a series of help columns. Put your data in A2:A999 (headers in Row...

help with cell referencing in named ranges
I have a named range covering about 30 columns of data. I'd like to lookup within the named range based on values in the 1st column, and a specific date which is the label on top of each of the other 29 columns. I have typed in a new column number as necessary, but I'd like to automate the column number by looking at a separate cell which gives the date. Ex: Dates across the top of the named range are 1/1/90, 1/1/91, etc thru 1/1/2003(in sheet1). Lets say I have a cell in sheet2(d1)that has the date 1/1/2003. In that same sheet starting in cell a1 I have a value which ...

Macro/message box to prompt user for their name and save name to cell
Hi, Any assistance would be greatly appreciated I need a macro that displays a message box which prompts the user fo their name. When the user enters their name the name is then the cop to a particular cell e.g. C2 Many thanks Ro -- Message posted from http://www.ExcelForum.com Rob, how about something like this, Sub test() MyName = InputBox("Enter Your Name", "What Is Your Name?") If MyName = "" Then Exit Sub End If Sheet1.Range("C2").Value = MyName End Sub -- Paul B Always backup your data before trying something new Please post any r...

Automatically changing the range in a graph
I constantly add data to a spreadsheet and I want to only graph the last 24 data points with out constantly cahnging the graph range. How do I do this? Jon Peltier has instructions for a dynamic chart: http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoLast12 Bret Davis wrote: > I constantly add data to a spreadsheet and I want to only graph the last 24 > data points with out constantly cahnging the graph range. How do I do this? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

automatically entering data into a cell
I'm not sure if this is possible but... I have three columns, a date in the first and last and text in the middle. When I enter a date in the start date I would like text to be automatically displayed (the text is active) in the middle column. Then when I enter a date in the end date column I would like the text in the middle column to change to complete. Any help is much appreciated. Many thanks --- Message posted from http://www.ExcelForum.com/ Hi For H2:J2: =IF(AND(H2="";J2=""),"",IF(ISERROR(DAY(H2)),"It's not start-date",IF(ISERROR(...

excel spread sheet wont caculate new data entered in ranges.
my excel spread sheets no longer adds new data entered in ranges. I use Excel to track construction expenses. Recently the workbook spreadsheets stoped adding new data entered into the ranges. Tools>Options>Calculation is set to "Manual"? The SUM range is too narrow and does not include the new data rows/columns? Gord Dibben Excel MVP On Mon, 7 Nov 2005 14:05:04 -0800, "Art-needs-help" <Art-needs-help@discussions.microsoft.com> wrote: >my excel spread sheets no longer adds new data entered in ranges. I use >Excel to track construction expenses....

Combining data (numeric format) in multiple cells into one cell (t
If you have data in multiple cells that is numeric format, can the data be combined into one cell as text? Example. Cell A1 is number of correct answers (9), Cell B1 is number of questions (10). In cell C1, would like to display data as (9-10)? Can a formula be created that will automatically do this so as to eliminate having to type the information? one way: =A1 & "-" & B1 In article <018B8827-4277-4C02-81E1-3BEDB61422A0@microsoft.com>, "GNAC SID" <GNAC SID@discussions.microsoft.com> wrote: > If you have data in multiple cells that is num...

Click on cell to take you to chart
I have a table of contents cover page (in excel) and 20 charts (each in it's own worksheet within the same document). I need to have the person be able to click on the cell indicating the chart title on the title page and have it take them to the corresponding chart. Is this possible? Right click on your cell that contains the title that you want to be able to click on, click on hyperlink...where it says Link To: choose Place in this Document...then it shows you the sheets that you can have it bring you to. Then you can type the Cell that you want it to jump to as well. ...

how do i mark range from A1 to DV5000 without scrolling?
Hi Is there a menu option to mark a range from A1 to some far off cell like DK4563 without scrolling all the way down and across? thanks Cricket :-) You can type the address, e.g., A1:DK4563, in the Name Box, which is to the left of the formula bar. This will select the specified range. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <cricketunes@yahoo.com> wrote in message news:1134001998.341982.309400@g47g2000cwa.googlegroups.com... > Hi > Is there a menu option to mark a range from A1 to some far off > cell > l...

Enter date in one cell, and the week ending date [Sunday] appears in the "W/E" cell
I have an XL 2003 workbook that I enter the date an event occured in cell C7. I want to make another cell [C2]display the week ending date [Sunday], based ono the date entered in cell C7. How can I accomplish this? Thanks, Tonso =7-WEEKDAY(C7,2)+C7 Daniel > I have an XL 2003 workbook that I enter the date an event occured in > cell C7. I want to make another cell [C2]display the week ending date > [Sunday], based ono the date entered in cell C7. How can I accomplish > this? > > Thanks, > > Tonso hi, =IF(WEEKDAY(C7)=1,C7,C7+(8-WEEKDAY(C7))) -- isabelle On ...

Refer to specific cell in a range
I would like to have a formula that refers to a specific (offset) cel in a range. For instance, range is three columns and 20 rows, and would like to refer to the cell in the first row and the third column. Should be simple ... Help appreciated -- maybeorno ----------------------------------------------------------------------- maybeornot's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1499 View this thread: http://www.excelforum.com/showthread.php?threadid=26614 First time I've tried this, but it seemed to work: =OFFSET(Range1,0,2,1,1) Where - Range...

Generating Unicode characters in a cell using formulas
With older multibyte Chinese code sets like Big5, I could create a large list or table of Chinese characherts by generating their two components with a formula like =CHAR(HEX2DEC(LEFT(A5,2)))&CHAR(HEX2DEC(RIGHT(A5,2))) were the hex code for the character (eg. A440) was in tyhe target cell A5. With Unicode, all I get are the two individual characters that (in Big5 anyway) made up the multi-byte code for a character. BTW: In the case above, Big5 A440 is Unicode U+4e00, the character for "one" δΈ€. Hi Geoff, You will have to create a User Defined Function for use on your w...

Format Cell Dialogue Box
Hi there, Why don't you make the Format Cells dialogue box modeless. So that you do not have to keep opening and closing it. Jimbola There are no "you"'s here, if your post is referring to Microsoft developers. This is a peer to peer News Group manned(personned?) by volunteers and not by Microsoft employees. Your "modeless" idea is a good one, however................ There are many shortcut keys for formatting cells without opening the dialog box. Perhaps one or more of them would suffice. See answer wizard on "shortcut keys" Gord Dibben Excel ...

trying to find how to display worksheet name in cell
I'm trying to determine the formula to display the worksheet name. http://contextures.com/xlfaqFun.html#SheetName From Debra Dalgleish's site. Yvonne Barber wrote: > > I'm trying to determine the formula to display the worksheet name. -- Dave Peterson ...

Move to next unprotected cell
Windows XP ,Office 2003 I have designed a form in which certain cells are protected. In some rows there is more than one unprotected cell, in others, only one unprotected cell. After entering data in an unprotected cell, pressing the 'Enter' key causes the curser to move to the first unprotected cell in the next row, whereas I require the curser to move to the next unprotected cell on the same row - and only moving to the next row when all cells on that row have been completed. How do I achieve this requirement? One way would be to use a worksheet_change event where you would ...

calculating in a changing range
I am trying to find a maximum value in a range that will be changing (in starting position, and length). Here is an example, this is cells A1 to A13: 2 1 0 1 2 3 4 3 2 1 0 1 2 I want to find the max between zero points (In this case, there would be one max, and it would be 4). The number of zero points and the number of cells between them will change. Anyone have some idea of how to go about this, or anything to even get me started? Thank you! -- gkaste ------------------------------------------------------------------------ gkaste's Profile: http://www.excelforum.com/member.php?...

Range Lookup
I want to do something like a vlookup but instead of looking for a specific number, I want to look for a range and then return a value. An example would be I want to return a letter grade based on test scores. I have the low end of the ranges in column A, high end in column B and in this case, the Grade would be in Column C. How do I do this without having to write a long IF statement? Try... =VLOOKUP(D1,$A$1:$C$10,3,TRUE) ....where D1 contains your lookup value. Hope this helps! In article <F969091F-B558-45EA-9761-C254AB1E6FF7@microsoft.com>, "Jake" <Jake@disc...

UDF: how to tell if optional *range* parameter is empty?
I have a UDF with an optional *range* parameter, and whether one block of code should run depends on whether or not a range was supplied. I tried ISMISSING(OptionalRangeParameter) but even when the range is not passed through, it apparently doesn't evaluate as missing. I also tried ISNULL and ISEMPTY, to the same effect. I also tried the ubound>lbound trick for arrays, but that didn't work (presumably because this is a range, or because the parameter hadn't been passed in the first place, I'm not sure) What is the most reliable method of determining if an...

Defined range using more than one column
Can anyone tell me if it is possible to have two or more columns in a defined range. I want to have a dropdown validation list where two or more columns of data is displayed on the dropdown list. When an item is selected in the dropdown list only one of the columns data is displayed in the cell. Is this possible and how is this accomplished? Or if there is a different approach I should go down please let me know. Thanks Pat You can have a defined range with two or more columns, but that won't show up in Data=>Validation using the list option. You might want to look at the Cont...