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

Steve,

Indeed I can do this with Vlookups - these are the formulas I
currently use. The reason I wanted to code it is two-fold. Firstly,
I'd like to add some error trapping, eg for when there is no Agent ID
to lookup to on the Lookup table (as it is a new agent for example) a
prompt to add new data would appear and seconly I need to cap all
commissions at a maximum of £3,000. Currently I overtype my formulas
to achieve this.

Finally, I'd like to use code so that I can add another feature. I'd
like to be able to check whether the Commission Due field has been
calculated already and if so go on to the next case. That is, when I
add new cases to the data the macro will run but only update the
additional cases and ignore the previously calculated ones.

I hope this helps.

Richard

"Steve Smallman" wrote in message ...
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