A
ajayb
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(F2>2500,"AN","PN"),IF(E2>7,"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,'Cesktop\[BBook.xls]Data'!$C:$C,'Cesktop\[BBook.xls]Data'!$I:$I))
Col P
=IF(ISNA(VLOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0)),"",(VLOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,5,0)))
Col Q =
=LOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C,'Cesktop\[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
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(F2>2500,"AN","PN"),IF(E2>7,"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,'Cesktop\[BBook.xls]Data'!$C:$C,'Cesktop\[BBook.xls]Data'!$I:$I))
Col P
=IF(ISNA(VLOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0)),"",(VLOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,5,0)))
Col Q =
=LOOKUP(C2,'Cesktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C,'Cesktop\[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