VBA with Select Case problems...

[alen.zakary]

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

 

[davesexcel]

goto data filter and see if that could help you at all

 

[Ardus Petus]

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

 

[Alen David]

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.

 

[DaveO]

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.

 

[DaveO]

Only downside to this approach is that your pricing data has to be in
ascending order else the vlookup will error.

 

[Tom Ogilvy]

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.

 

[DaveO]

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

 

[Alen David]

No worries!

I got it to work by setting it to TRUE!

THANKS TO EVERYONE, for any information you provided me!

Alen

 

[Tom Ogilvy]

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.