In each cell of that grid I have the following formula which calculates whether part of the deal is maturing in the month and if so how much.

Obviously having this formula in there so many times has caused the file size to grow, it is now over 15MB.

=IF(RIGHT($D148,2)="CB",IF(EOMONTH($M148,0)=HP$6,$H148,""),(IF(MONTH(EOMONTH($K148,0))<>MONTH(HP$6),"",(IF(OR((YEARFRAC(EOMONTH($K148,0),HP$6,0))<1,((YEARFRAC(EOMONTH($K148,0),HP$6,0))>COLUMNS(amortization)-1),(YEAR(EOMONTH($K148,0))>YEAR(HP$6))),"",IF(VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)=0%,"",VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)*$H148))))))

I would like to embed this formula in the VBA editor so that it still does exactly what it does now and returns a value in each cell where it should, but I don't want it taking up so much space!

Is there any way I can do this??

Thanks