Select case syntax for continuous range values?

I know you can use Select case statements to cover a range of integer 
values, such as:

Case 100 To 500
Case 501 to 650
etc

However, I'm not clear on how to work with non-integer values to ensure that 
every possible value is covered, while ensuring no overlap in categories. 
With an IF statement I might use:

If x >100 and x <=500
elseif x >500 and x <=650

which would properly assign a value of 500.4395 to the second condition

Is there a way to do this with select case? Or is my best option to try to 
use more decimals than the data might need, e.g.

Case 100 To 500
Case 500.000001 to 650

Thank you,
Keith

0
Utf
12/16/2009 5:51:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
833 Views

Similar Articles

[PageSpeed] 55

You can step thru the code below that I gave and you'll see that the case 
where x = 500.4395 the Select Case Statement will work.  Change the x 
variable to whatever you like and you'll see.

Sub Select_Case()

Dim x As Double

    x = 500.4395

    Select Case x
        Case Is <= 100
            MsgBox "x is Less than 100"
        Case 100 To 500
            MsgBox "x is Between 100-500"
        Case 500 To 650
            MsgBox "x is Between 500-650"
        Case 650 To 1000
            MsgBox "x is Between 650-1000"
        Case Else
            MsgBox "x is Greater than 1000"
    End Select

End Sub

Excel VBA evaluates each Case to see if it is true before moving to the next 
Case.  If the Case is true your code under the Case is executed, if False the 
next Case is evaluated.

Hope this helps!  If so, click "YES" below.
-- 
Cheers,
Ryan


"ker_01" wrote:

> 
> I know you can use Select case statements to cover a range of integer 
> values, such as:
> 
> Case 100 To 500
> Case 501 to 650
> etc
> 
> However, I'm not clear on how to work with non-integer values to ensure that 
> every possible value is covered, while ensuring no overlap in categories. 
> With an IF statement I might use:
> 
> If x >100 and x <=500
> elseif x >500 and x <=650
> 
> which would properly assign a value of 500.4395 to the second condition
> 
> Is there a way to do this with select case? Or is my best option to try to 
> use more decimals than the data might need, e.g.
> 
> Case 100 To 500
> Case 500.000001 to 650
> 
> Thank you,
> Keith
> 
0
Utf
12/16/2009 6:01:02 PM
Reply:

Similar Artilces:

Subscript Out of Range Error
For some reason, I get the Subscript Out of Range Error each time I open Excel for the first time. Once I answer the message, then I can work with existing or new workbooks with no problem. Does anyone know a straighforward (simple) way to get rid of this error for good? I actually believe that I set something up accidentally--possibly in the Format menu. HELP!!! Hi sounds like an addin with errors. Have you installed such addins? -- Regards Frank Kabel Frankfurt, Germany "Barb" <barb@americanroller.com> schrieb im Newsbeitrag news:969cb8ce.0410111028.39b94ad2@posting.go...

outputting values to a range from one formula
I wish to generate a table automatically by means of a single formula that applies an iteration on a starting given value with a given step, and the computed values are posted/entered automtically into cells from a given cell onwards, say below it, until the computed value reaches a certain given limt. This is somehow the inverse of INDIRECT or of OFFSET. These can pull values from a variable addresses of cells. What I need is to push values into a variable addresses of cells. Can anyone help me on that?? Thanks. :confused -- Shafe ---------------------------------------------------------...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

how to select a column from a cell after find
I am trying to set up the default format for date a column once I found the cell with the heading from C# oRng = oSheet.UsedRange; Excel.Range oR = oRng.Find("Date", omissing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, omissing, omissing); I'm now atloos as to how to get column selected or apply format to a col. string s = oR.ToString(); i was searching tje ms sites w/o sucesss. can someone give a hint? thx GS;687486 Wrote: > I am trying to set up th...

VLookup not returning the last value
Hello, I am using VLOOKUP =VLOOKUP(D7,X4:X7:Y4:Y7,2,FALSE) My problem is that X7 is not showing in the dropdown list. The dropdown is simple text a value just like the other rows. Can anyone help me understand why I don't see this value? Thanks very much! >>VLOOKUP(D7,X4:X7:Y4:Y7,2,FALSE) try VLOOKUP(D7,X4:Y7,2,FALSE) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "George" <George@discussions.microsoft.com> wrote in message news:07C02EA4-F498-4A0D-B161-5DD5411520C9@microsoft.com... > Hello, > I am using VLOOKUP =VLOOKUP(D7,X...

Is there a formula to place a value into another cell?
Is there a formula to place a value into another cell? For example, can I have a formula in cell 'A2' that places the value "AAA" into cell 'A1'? Thanks! No, a cell can only "pull" information. You would need a macro to do what you want. chillynois@yahoo.com wrote: > Is there a formula to place a value into another cell? > > For example, can I have a formula in cell 'A2' that places the value > "AAA" into cell 'A1'? > > Thanks! > chillynois, Technically, you could come up with a UDF (user defined fu...

to Upper Case
How do I go about converting an entire spreadsheet from Title Case or lower case to UPPER CASE? Thanks Check out http://www.mvps.org/dmcritchie/excel/proper.htm#upper In article <#3SY9DXdDHA.568@TK2MSFTNGP11.phx.gbl>, "Ridimz" <ridimz@SPAMyahooFREE.com> wrote: > How do I go about converting an entire spreadsheet from Title Case or lower > case to UPPER CASE? > > Thanks > > ...

Adding "moving" ranges
This may be simplier than I can think of right now. I have 12 monthly budgets on a worksheet. Two of the columns in each budget indicate INCOME and EXPENSES. One income is fixed on the 15th and 30th and the other income is every two weeks, which is where I come up with the "moving" range. Here is a sample view: Exp1 Exp2 Exp3 Inc1 <--Every 2 weeks Exp 4 Exp 5 Inc2 <--15th Exp 6 Inc 1 <--Every 2 weeks Exp 7 Exp 8 Inc 2 <--30th Exp 9 I want to be able to have a formula that can locate each income and...

Selecting local printer
I'm sure this is really easy, but I can't find the answer. I want to select from one of the locally configured printers (similar to the Printer Name dropdown in the Print dialogue), but without using a full Print dialogue. ConnectToPrinterDlg doesn't really do what I want because I don't want to browse network printers. Can you help please? Check out the EnumPrinters API call and PRINTER_INFO_2 (other numvers like _4) etc News wrote: > I'm sure this is really easy, but I can't find the answer. > > I want to select from one of the locally configure...

Sum column values that contains text &/or dates
Column D3 thru D43 contain a mixture of values & dates. For example: D3 = 16,026.76 D4 = 11-18-05 D5 = blank D6 = 6,855.34 D7= 11-03-05 D8 = blank D9 = 5,270.00 D10 = 11-02-05 Need sum to equal 28,152.10 (total only values from entry or formulas) Thanks so much. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=489114 If your dates are text strings which say "11-18-05"...

Customise case view to include all contacts for specified parent
I need to be able to display all open cases for a particular Account, however the Customer field contains the contact who reported the case, not the account name. Can anyone help? Alan You cannot do this with the product alone, you'll need a custom rollup navbar (side tab) built either through the SDK or by a 3rd party ISV add-in "Alanb" <Alanb@discussions.microsoft.com> wrote in message news:1776F6E6-337A-4924-B7AC-8234DA55E3AC@microsoft.com... >I need to be able to display all open cases for a particular Account, >however > the Customer field contains th...

Drop Down box
Good luck understanding my confusing description below: I am making an invoice template ("InvoiceWorksheet") to be used fo multiple products from multiple manufacturers. I would like to select a product from a dropdown box A1 and have al subsequent fields automatically populate with information about m chosen product based on info from the "DataWorksheet" (a secon worksheet within this Excel file). _InvoiceWorksheet_setup:_ Cell A1 - I select BlueWidget from a dropdown. Cell A2 - automatically populates with BlueWidgetManufacturerName. Cell A3 - automatically populates...

choose last cell with a value in a column
need to figure a function that identifies the last cell with a value (could be a number or a letter) in a column....I will be updating this every other day, it should be able to identify the next cell if I add a value to the column. for your help I am very thankfull. =SUMPRODUCT(MAX((ROW(A1:A65535))*(A1:A65535<>"")))+(A65536<>"") will tell you where the last value is, number or not. -- Gary''s Student - gsnu200909 "jcheko" wrote: > need to figure a function that identifies the last cell with a value (could > be a...

Automatic Customer Cases?
On page 72 of the User Guide, it says... Opening a Case When a customer submits a case, he or she is automatically sent a system-generated e-mail message that provides the date the case was created, a case ID number for tracking, a title and description of the case, and any other information, as appropriate. How does a customer submit a case to the CRM system? And how do I have CRM automatically send out the Case information? -Vito On Mon, 2 Aug 2004 19:58:24 -0400, "Vito DeCarlo" <FirstInitialLastName@ThePulse.com> wrote: >On page 72 of the User Guide, it says... &...

Using Select Case on Single column only
I'm writing code to standardize a report I get from someone. 1. I need to change the single character value in each cell to the actual name using Select Case Example if cell value = B then "Bacon" else if value = C then "Cheese" else if value = E then "Eggs" etc I've gotten the following code using example found here but I'm stuck on defining my column range. My code works as long as there are no blank cells. and how do I use test each cell and stop at last cell of column even if there is a blank row. My code Start Sub FixReport()...

Total Textbox Values on a form
I have 3 textboxes on my form (with counts from my database) I have a 4th (unbound) textbox to show the total of the values of the 3 textboxes. I tried, as the controlSource for tbx4: =tbx1 + tbx2 + tbx3 =tbx1.value + tbx2.value + tbx3.value =sum(tbx1.value + tbx2.value + tbx3.value) Each gives an error. On a form, how do I total the values from three textboxes in a 4th textbox? TIA We aren't there. We can't see your PC. We have no idea what error you received. If you'll post the error message, maybe that will help in diagnosing what's happening. Ev...

CString value
I want to know how to assign the CString value into VARIANT type. How to convert? VARIANT vt; CString st; vcteam <anonymous@discussions.microsoft.com> wrote in message news:05d501c3c0cc$7e644df0$a001280a@phx.gbl... > I want to know how to assign the CString value into > VARIANT type. > How to convert? > > VARIANT vt; > CString st; > CString st; .... VARIANT vt; vt.vt=VT_BSTR; vt.bstrVal=st.AllocSysString(); Thanks a lot. >-----Original Message----- >vcteam <anonymous@discussions.microsoft.com> wrote in message >news:05d501c3c0cc$7e644df0$a001...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

Before Update on record select
I am getting no where fast. I have a main form and sub form and what i need is a message to appear when user moves on to the next record using the record selector on the bottom of the form. I have tried the beforeupdate on the form but no results. Can form design prevent a message prompt? One problem is that if no changes to the current record have been changed, the Before Update event will not fire. If you do make a change, the code as is should work. I have gotten into the habbit of not using the standard record navigators, but write my own so I can easily deal with this. -- Dave Ha...

Case Assigned History
Is there any way if a case is assigned to users and back again, that history can be tracked in 1.2? Thanks, ...

Tell the difference between upper and lower case
I have a column that has letters in it and I am using =COUNTIF(D13:D27,"M") in one cell and =COUNTIF(D13:D27,"m") in another. to keep track of what letters are put in the "D" column. But the both count the upper AND lowercase letters. Is there any way to mak excel tell the difference between upper and lowercase letters? Thanks Ro -- Message posted from http://www.ExcelForum.com RogerDaShrubber wrote... ... >=COUNTIF(D13:D27,"M") in one cell > >and > >=COUNTIF(D13:D27,"m") in another. > >to keep track of what lette...

#VALUE error
I am using Excel from Office 2007. I transfered a spreadsheet program from a Works spreadsheet, which I had adapted from one supplied with the program. It calculates your mortgage payments. I adapted it to calculate ROI from several variables on prespective rental properties. In Excel I get a #VALUE error when a cell conditionally references another cell that references another cell. I have attached several of the cell functions. =IF(AND(F5,F6,F7),PMT((F6/12)/100,F11,-F5),"") =IF(AND(G8,G9,G10),PMT((G9/12)/100,G13,-G8),"") =IF(G24,G24/G5,"") The online ...

Averaging Negative Values
I have data which has both positive and negative values which represent distances form a point. The fact they are negative shows the direction of the distance but I want to average them without taking into account some of the numbers are negative e.g. 0.010 -0.005 0.006 Answer should be 0.007 for the average. How do I get excel to ignore the fact some are negative but not ignore the number itself? -- Regards Andy Andy Roberts Win XP, Office 2007 You could put: =3DABS(A1) in a helper column, copy down, and then take the average of that column. If you want to avoid using a...

ListView selection chenged
Hello guys Is there an event handler to detect if the selection in the ListView (ListCtrl) has chenged? Regards, Mystique >Is there an event handler to detect if the selection in the ListView >(ListCtrl) has chenged? Try the LVN_ITEMCHANGED notification message. You have to check the message parameter for the specific attribute you're after. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq ...

selection of a cell extends it's attributes into other cells
I have a number of worksheets where when you click on certain cells, th format of that cell expands into adjacent cells. For example, I select cell D4 and cell D4 and D3 appear to merger. This is only whilst the cell is being amended and they haven't actuall merged, it only appears that way. I have it happening where cells "merge" with other cells vertically o horizontally. It only ever happens with ONE adjacent cell, it is consistent in tha it happens whenever I try it and it always "merges" with the sam cell. It looks like a straightforward bug to me - has anyone ...