I am trying to building a simple commission calculator at work to make things easier using VBA

Have about 15 countries we trade in with commssions broken down as follows (data as shown on spead
sheet):

A B C D E F G H I J

Values up to up to up to up to up to up to up to up to and up
5,000 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000
Countries
9 U.K. $80. $130. $150. $165. $180. $200. $300. $375. $400.
10 Hong Kong $120. $150. $185.
11 Singapore $250. " " " "
12 South Africa " " " "
13 France " "
14 Germany " "
" " etc.
" "
" "
etc.

In cells A2, I created a pull down menu where you can select the appropriate country [ data /
validation . . .]
In cell A4 = (quantity) and cell A5 = (price) giving the Value amount in cell A7 =(a4*a5)
I would like the correct commission to appear in cell A8

I have started the following code in VBA for this project however having difficulty to get it to
work also not sure if it is the most efficient way of doing things, I am a novice, still learning
and trying to keep it as simple as possible.

If cells(9, "a").Value = "U.K." and cells(7, "a").Value 0 <=5000
Then cell(8, "a") = cells(9, "b").Value
Elseif cells(9, "a").Value = "U.K." and cells(7, "a").Value 10000 <=20000
Then cell(8, "a") = cells(9, "c").Value
Elseif cells(9, "a").Value = "U.K." and cells(7, "a").Value 5000 <=10000
Then cell(8, "a") = cells(9, "d").Value
" "
" "
If cells(9, "a").Value = "Hong Kong" and cells(7, "a").Value 0 <=5000
Then cell(8, "a") = cells(10, "b").Value
Elseif cells(9, "a").Value = "Hong Kong" and cells(7, "a").Value 10000 <=20000
Then cell(8, "a") = cells(10, "c").Value
" "
" "
Else: cells(8, "a") = ""
End if


Your help is appreciated

You can do all of that with one formula in cell A8
=VLOOKUP(A2,A7:J15,MATCH(A7-1,B7:J7,1)+2,FALSE)
where
A2 is the country
A7:J15 is the table with the countries in the leftmost
column (stretch as required)
the MATCH formula returns the column in the table
A7 is the amount (subtract 1 to get proper match)
B7:J7 is the list of commission cut-off values
1 forces MATCH to return position equal to or less than
the value
FALSE forces VLOOKUP to get exact match on country

Kevin Beckham

-----Original Message-----
I am trying to building a simple commission calculator at
work to make things easier using VBA

Have about 15 countries we trade in with commssions
broken down as follows (data as shown on spead
sheet):

A B C D E F
G H I J

Values up to up to up to up to up
to up to up to up to and up
5,000 10,000 20,000 30,000
40,000 50,000 60,000 70,000 80,000
Countries
9 U.K. $80. $130. $150. $165.
$180. $200. $300. $375. $400.
10 Hong Kong $120. $150. $185.
11 Singapore $250. " " " "
12 South Africa " " " "
13 France " "
14 Germany " "
" " etc.
" "
" "
etc.

In cells A2, I created a pull down menu where you can
select the appropriate country [ data /
validation . . .]
In cell A4 = (quantity) and cell A5 = (price) giving the
Value amount in cell A7 =(a4*a5)
I would like the correct commission to appear in cell A8

I have started the following code in VBA for this project
however having difficulty to get it to
work also not sure if it is the most efficient way of
doing things, I am a novice, still learning
and trying to keep it as simple as possible.

If cells(9, "a").Value = "U.K." and cells(7, "a").Value
0 <=5000
Then cell(8, "a") = cells(9, "b").Value
Elseif cells(9, "a").Value = "U.K." and cells
(7, "a").Value 10000 <=20000
Then cell(8, "a") = cells(9, "c").Value
Elseif cells(9, "a").Value = "U.K." and cells
(7, "a").Value 5000 <=10000
Then cell(8, "a") = cells(9, "d").Value
" "
" "
If cells(9, "a").Value = "Hong Kong" and cells
(7, "a").Value 0 <=5000
Then cell(8, "a") = cells(10, "b").Value
Elseif cells(9, "a").Value = "Hong Kong" and cells
(7, "a").Value 10000 <=20000
Then cell(8, "a") = cells(10, "c").Value
" "
" "
Else: cells(8, "a") = ""
End if


Your help is appreciated


.

Hi Billabong,

You can write formula in cell A8 but have to change your List of
commission cut-off values in first row from

5,000 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000

to as under

0 5,000 10,000 20,000 30,000 40,000 50,000 60,000
70,000


Now put this formula in cell A8

=IF(A70,INDEX(B9:J15,MATCH(VLOOKUP(A2,A9:A15,1,0) ,A9:A15,0),MATCH(HLOOK
UP(A7,B1:J1,1),B1:J1,0)),0)

Where

A7 = Total
B9:J15 = List of Commission Amount
A2 = Selected Country
A9:A15 = List of Country
B1:J1 = List of commission cut-off values

If you want demo workbook e-mail me.

Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!