VBA with Select Case problems...

[[email protected]]

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

<[email protected]> a écrit dans le message de
news:[email protected]...
> 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
>

[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.

"[email protected]" 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
>
>

[DaveO]

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
>
> <[email protected]> a écrit dans le message de
> news:[email protected]...
> > 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
> >
>
>
>

[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.

--
Regards,
Tom Ogilvy


"DaveO" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> > <[email protected]> a écrit dans le message de
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

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

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > > <[email protected]> a écrit dans le message de
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

[Alen David]

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 mu=
st
> 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 FALS=
E,
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > > =3DVLOOKUP(E2,A2:C18,3,1) will give you the pricing.
> > > >
> > > > NB: I don't use the "To" column.
> > > >
> > > > HTH
> > > > --
> > > > AP
> > > >
> > > > <[email protected]> a =E9crit dans le message de
> > > > news:[email protected]...
> > > > > 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 prin=
t 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 y=
ou
> > 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 t=
o get
> > > > > things to work this is my poor excuse for a Select Case:
> > > > > -----------------------------------------------------------------=
-----
> > > > > Sub bwo()
> > > > > Dim clicks As Integer
> > > > > Select Case clicks
> > > > >
> > > > > Case Is >=3D 1, Is <=3D 99
> > > > > clicks =3D 100
> > > > > Case Is >=3D 100, Is <=3D 249
> > > > > clicks =3D 90
> > > > > Case Is >=3D 250, Is <=3D 499
> > > > > clicks =3D 80
> > > > > Case Is >=3D 500, Is <=3D 999
> > > > > clicks =3D 70
> > > > > Case Is >=3D 1000, Is <=3D 2499
> > > > > clicks =3D 60
> > > > > Case Is >=3D 2500, Is <=3D 4999
> > > > > clicks =3D 50
> > > > > Case Is >=3D 5000, Is <=3D 7499
> > > > > clicks =3D 48
> > > > > Case Is >=3D 7500, Is <=3D 9999
> > > > > clicks =3D 46
> > > > > Case Is >=3D 10000, Is <=3D 12499
> > > > > clicks =3D 44
> > > > > Case Is >=3D 12500, Is <=3D 14999
> > > > > clicks =3D 42
> > > > > Case Is >=3D 15000, Is <=3D 17499
> > > > > clicks =3D 40
> > > > > Case Is >=3D 17500, Is <=3D 19999
> > > > > clicks =3D 38
> > > > > Case Is >=3D 20000, Is <=3D 22499
> > > > > clicks =3D 36
> > > > > Case Is >=3D 22500, Is <=3D 24999
> > > > > clicks =3D 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 t=
hen
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >=20
> >=20
> >

[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.

--
Regards,
Tom Ogilvy



"DaveO" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> > > > <[email protected]> a écrit dans le message de
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >