| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA with Select Case problems...
Can someone help me figure this out?
I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
goto data filter and see if that could help you at all -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=517877 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
Assuming your pricing list is in A2:C18 and your page number in E2
The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
The Data filter will show only values sorted, which I assigned. It
doesn't seem to work when I am trying to reference another sheet and retrieve a value for a price. Thanks Dave. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
Do you have to use VBA?
I ask as you could use a SUMPRODUCT formula to work this out in 1 cell. As an example, say cell A1 has the number of pages the person wants and B1 should be the cost and the table you have provided in your example is in A5:C50 (for example). Your formula should be ... =SUMPRODUCT(--(A5:A50=A1), --(B5:B50<A1), (C5:C50)) I appreciate this is a simple example, but a cell formula can work out your costs without the need for any VBA! HTH. Dave. " wrote: Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
Only downside to this approach is that your pricing data has to be in
ascending order else the vlookup will error. "Ardus Petus" wrote: Assuming your pricing list is in A2:C18 and your page number in E2 The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
Only downside to this approach is that your pricing data has to be in
ascending order else the vlookup will error. That isn't correct. Take at the Excel help on VLOOKUP to see the options. -- Regards, Tom Ogilvy "DaveO" wrote in message ... Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. "Ardus Petus" wrote: Assuming your pricing list is in A2:C18 and your page number in E2 The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
My apologies, Excel help file says ...
If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. So although it won't error, for which I was incorrect, it could produce an incorrect result. Tom - Would you have thought the SUMPRODUCT was a way to go?? "Tom Ogilvy" wrote: Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. That isn't correct. Take at the Excel help on VLOOKUP to see the options. -- Regards, Tom Ogilvy "DaveO" wrote in message ... Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. "Ardus Petus" wrote: Assuming your pricing list is in A2:C18 and your page number in E2 The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
No worries!
I got it to work by setting it to TRUE! THANKS TO EVERYONE, for any information you provided me! Alen DaveO wrote: My apologies, Excel help file says ... If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. So although it won't error, for which I was incorrect, it could produce an incorrect result. Tom - Would you have thought the SUMPRODUCT was a way to go?? "Tom Ogilvy" wrote: Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. That isn't correct. Take at the Excel help on VLOOKUP to see the options. -- Regards, Tom Ogilvy "DaveO" wrote in message ... Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. "Ardus Petus" wrote: Assuming your pricing list is in A2:C18 and your page number in E2 The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA with Select Case problems...
No, in this case, it looks like his table is sorted and he wants that type
of match. So a lookup function would work best. -- Regards, Tom Ogilvy "DaveO" wrote in message ... My apologies, Excel help file says ... If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted. So although it won't error, for which I was incorrect, it could produce an incorrect result. Tom - Would you have thought the SUMPRODUCT was a way to go?? "Tom Ogilvy" wrote: Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. That isn't correct. Take at the Excel help on VLOOKUP to see the options. -- Regards, Tom Ogilvy "DaveO" wrote in message ... Only downside to this approach is that your pricing data has to be in ascending order else the vlookup will error. "Ardus Petus" wrote: Assuming your pricing list is in A2:C18 and your page number in E2 The formula =VLOOKUP(E2,A2:C18,3,1) will give you the pricing. NB: I don't use the "To" column. HTH -- AP a écrit dans le message de oups.com... Can someone help me figure this out? I have two sheets, 1. Pricing 2. Ordering On the "Ordering" sheet I have to enter numbers of sheets to print for a customer. Based off of the number of sheets someone wants me to print, I have to goto my "Pricing" sheet, and look to see which pricing category it will fall under. For example: I have a print job for 250 pages, now I have to create a formula that will goto my "Pricing" sheet, and look through the following conditions. ---------------------------------------------------------------------- PRICING SHEET From To Charge Amount 1 99 $0,100 100 249 $0,090 250 499 $0,080 500 999 $0,070 1000 2499 $0,060 2500 4999 $0,050 5000 7499 $0,048 7500 9999 $0,046 10000 12499 $0,044 12500 14999 $0,042 15000 17499 $0,040 17500 19999 $0,038 20000 22499 $0,036 22500 24999 $0,034 25000 27499 $0,032 27500 29999 $0,030 30000 60000 $0,028 ---------------------------------------------------------------------- I tried doing this as an IF statement, but I soon realized that you can only nest up to 7 IF's. :( I tried to create 3 Nested IF formulas by following this example and I kept on recieving errors. http://www.cpearson.com/excel/nested.htm This what I created, but am not quite understanding exactly how to get things to work this is my poor excuse for a Select Case: ---------------------------------------------------------------------- Sub bwo() Dim clicks As Integer Select Case clicks Case Is = 1, Is <= 99 clicks = 100 Case Is = 100, Is <= 249 clicks = 90 Case Is = 250, Is <= 499 clicks = 80 Case Is = 500, Is <= 999 clicks = 70 Case Is = 1000, Is <= 2499 clicks = 60 Case Is = 2500, Is <= 4999 clicks = 50 Case Is = 5000, Is <= 7499 clicks = 48 Case Is = 7500, Is <= 9999 clicks = 46 Case Is = 10000, Is <= 12499 clicks = 44 Case Is = 12500, Is <= 14999 clicks = 42 Case Is = 15000, Is <= 17499 clicks = 40 Case Is = 17500, Is <= 19999 clicks = 38 Case Is = 20000, Is <= 22499 clicks = 36 Case Is = 22500, Is <= 24999 clicks = 34 etc.... End Select End Sub ---------------------------------------------------------------------- Based off of the number from my "Order" sheet I need to check it with the pricing range to see which category my value falls under from my example of 250 the price that I should apply is 80 dollars, and then apply this price to the field on the "Ordering" sheet in a Cell. I hope I didn't butcher this explanation, I tried to give as much info as I can. Someone please help me......... Alen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| select case | Excel Discussion (Misc queries) | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case | Excel Programming | |||
| Problems with select case | Excel Programming | |||
Linear Mode
