Hi All,

I wonder if you can help with a huge problem? I have a worksheet with
64,519 rows and 24 columns (A-X). The workbook is currently 80MB in size
because the columns I-X have functions in. They are as follows:

Col I
=IF(H2="False",IF(F22500,"AN","PN"),IF(E27,"AN", IF(ISNA(VLOOKUP(A2,Parishes!A:B,2,0))," ",VLOOKUP(A2,Parishes!A:B,2,0))))

Col J =IF(ISNA(VLOOKUP(G2&D2&E2,'Lamps
Table'!D:E,2,0)),"",(VLOOKUP(G2&D2&E2,'Lamps Table'!D:E,2,0)))

Col K =IF(ISNA(VLOOKUP(J2,'Lamp Watts'!A:B,2)),"",(VLOOKUP(J2,'Lamp
Watts'!A:B,2)))

Col L =IF($I2="PN",(O2*2335)/1000,(O2*4136)/1000)

Col M =IF(I2="PN",L2,(L2/2)+((L2/2)*0.7))

Col N =M2*'C:\Desktop\[ABook.xls]Factors'!$B$1

Col O
=IF(H2="True",K2,LOOKUP(C2,'C:Desktop\[BBook.xls]Data'!$C:$C,'C:Desktop\[BBook.xls]Data'!$I:$I))

Col P
=IF(ISNA(VLOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0)),"",(VLOOKUP(C2,'C:Desk top\[CBook.xls]PFIEnergy7.rpt'!$C:$H,5,0)))

Col Q =
=LOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$H:$H)

Col R =IF(I2="AN",(4136*Q2)/1000,(2335*Q2)/1000)

Col S =IF(ISNUMBER(SEARCH("SON",$P2,1)),"Compliant", "Not Compliant")

Col T =IF(ISNUMBER(SEARCH("PLL",$P2,1)),"Compliant", "Not Compliant")

Col U =IF(ISNUMBER(SEARCH("PLT",$P2,1)),"Compliant", "Not Compliant")

Col V
=IF(S2="COMPLIANT","COMPLIANT",IF(T2="COMPLIANT"," COMPLIANT",IF(U2="COMPLIANT","COMPLIANT","NOT COMPLIANT")))

Col W =IF(H2="True",IF(V2="COMPLIANT","COMPLIANT"),"NOT Compliant")

Col X =IF(V2="COMPLIANT",R2,M2)

And obviously repeat for all the rows.

What I am after is some help in creating a macro that will work out the
calculations in each of the columns and paste the values in the cells. I
know I've seen a book do this before but I'm at a loss as to where to start.
Would really like it as simple as possible so that I can understand it and
take on board the knowledge.

Many thanks

Andy