Richard,

do you need to do this programmatically or will a vlookup (well two
actually) do?

from the data you provide I assume that Agent001 gets 150+.005 of all 45
cases therefore your formula could be something like

=if(A2<50000000,
vlookup(c2,lookuprange,2,0)+vlookup(c2,lookuprange ,3,0)*b2,vlookup(c2,lookup
range,4,0)+vlookup(c2,lookuprange,5,0)*b2)

Steve

"Richard" wrote in message
om...
I would like some code that can programatically add the commission due
to the next column in my table of cases. Basically each case has an
attached agent id. According to the type of case different commission
is paid. I have a list of each agent id and the relevant commissions
due. I need to look at the type of case and then lookup the agent id
to calculate the correct commission due.

Sample Cases Table Data

Case Loan Amount Agent Id Commission Due
45123456 £30,000 AGENT001 To be calculated by macro
55654321 £55,000 AGENT024 To be calculated by macro

Sample Lookup Table

Agent Id 45 Case 45 Case %ge 55 Case 55 Case %ge
Amount of Loan Amount Amount of Loan Amount

AGENT001 £150 0.5% £0 0.5%
AGENT002 £0 1.5% £0 1.0%

There are many more agents and differing amounts but they all follow
the above pattern. Basically I need code that will look at the agent
id and lookup to the commission table. Then according to whether it is
a case beginning "45" or "55" lookup the relevant commission amounts,
calculate what is to be paid and then paste the value into the cases
table Commission Due column.

I hope this explains it well enough and appreciate any help and advice
you can give.

Regards,

Richard