Replacing vlookup and rank w/ code

[daseffects]

I have a model that calculates a final % by multiplying base data by a series of events in the order of the events date rank. The calc works fine but I've added some functionality that updates the events and causes the model to calc very slowly due to the current vlookup rank method.

What I'm looking to do is replace the current method w/ some code that calcs the results then pastes it into the sheet which can be launched w/ a command button and its a bit beyond my coding skills

I've mocked up the current process below:

BASE DATA
2000 2001 2002
A 50% 40% 30%
B 40% 30% 20%
C 25% 40% 40%

Events
ProdRank Product 2000 2001 2002 DATE RANK
A2 A 10% 10% 15% 1/1/2001 2
A3 A 5% 5% 20% 1/2/2002 3
A1 A 5% 12% 30% 1/3/1999 1
B1 B 5% 0% 0% 1/4/2001 1


Prodrank = Product & Rank
Rank = RANK(DATE,DATERANGEA,1)

CALC
2000 2001 2002
A 60.64% 48.51% 36.38%
B 42.00% 31.50% 21.00%
C 25.00% 40.00% 40.00%

The formula in the above cells is the very painful

B2=(BASEC6*(1+IF(ISERROR((VLOOKUP((CALC$A2&1),EVENTS,3,FALSE))),0,((VLOOKUP((CALC$A2&1),EVENTS,3,FALSE))))))*(1+IF(ISERROR((VLOOKUP((CALC$A2&2),EVENTS,3,FALSE))),0,((VLOOKUP((CALC$A2&2),EVENTS,3,FALSE)))))*(1+IF(ISERROR((VLOOKUP((CALC$A
2&3),EVENTS,3,FALSE))),0,((VLOOKUP((BASE$A2&3),EVENTS,3,FALSE)))))

In the final model I could conceivably have over 50 events so you can see that the above method probably won't support that level of lookups

DAS

 

[ETID]

Look in the vba help on "application.worksheetfunction"