Need help on Logical Statements

[billabong]

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

 

[Kevin Beckham]

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

 

[Shailesh Shah]

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(A7>0,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/