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


.