Complicated Calculation Macro

Hi all,

I need help making a macro to run a calculation that will result in about 80,000+ records and growing. This will result in 2 separate worksheet outputs since I have Excel 2002 SP3 and it only goes up to 65,000 per worksheet.

The calculation will calculate the cost of a specific item to a particular destination. Right now there are about 120 locations and a little less than 700 items (hence roughly 80k unique combinations). Both of these variables will grow over time that is why I need a macro to run this twice a week or so to keep things up to date.

Right now there is a drop-down already made to select the item location and item # in the 1st worksheet and the calculation works just fine with the index-match array that is in the "delivered cost" cell.

I need some kind of looping macro to do this and the calculation step per item is:

Delivery cost to location divided by the cartons/container to get the freight adder, then the freight adder is added to the cost/container to give us the landed cost result.

The following is an example:

DDP_Cost CTNs/Container Freight Cost/CTN Landed_Cost
$909 864 $1.05 $28.38 $29.44


Right now the output worksheets is on the attached workbook as All_Precision_Cost_P1 & P2 and all the input worksheets are also in the workbook.
**All names and data have been changed to protect proprietary information**

I hope someone can help me as I have been stuck on this for almost 2 weeks as I am just a beginner at programming. I guess the biggest problem is that the worksheets inputs have to stay in that format as it will be a shared workbook with other users added item # information as we add it to stock and I don't know how to program without the specific rows.


Much Thanks.

Oops I cannot even post the sample workbook due to security settings of the company internet so if anyone has any questions please ask and I will describe it the best I can.



Much Thanks.