Thread: Write values of the formulas in to the cells instead of using formula in the cell

I have the below formula in C1,C2 and goes on about 4000 rows.When i change the values in the sheet it slows down and calculate after a long time.Could it be possible to calculate formula values for each cell by excel macro and write only the value instead of formula.

C1
=(IF(DAY(M1)<10;"0"&DAY(M1);DAY(M1))&IF(MONTH(M1)<10;"0"&(MONTH(M1));MONTH(M1))&RIGHT(YEAR(M1);2))&" "&N1&" "&LEFT(F1;4)&" "&IF(ISERROR(IF(SEARCH(",";B1;1)=3;LEFT(B1;2)&"."&RIGHT(B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=2;LEFT(B1;1)&"."&RIGHT(B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=4;LEFT(B1;3)&"."&RIGHT(B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=0;B1;"YOK")))));B1;IF(SEARCH(",";B1;1)=3;LEFT(B1;2)&"."&RIGHT( B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=2;LEFT(B1;1)&"."&RIGHT(B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=4;LEFT(B1;3)&"."&RIGHT(B1;LEN(B1)-SEARCH(",";B1;1));IF(SEARCH(",";B1;1)=0;B1;"YOK")))))&" "&VALUE(RIGHT(F1;3))

C2
=(IF(DAY(M2)<10;"0"&DAY(M2);DAY(M2))&IF(MONTH(M2)<10;"0"&(MONTH(M2));MONTH(M2))&RIGHT(YEAR(M2);2))&" "&N2&" "&LEFT(F2;4)&" "&IF(ISERROR(IF(SEARCH(",";B2;1)=3;LEFT(B2;2)&"."&RIGHT(B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=2;LEFT(B2;1)&"."&RIGHT(B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=4;LEFT(B2;3)&"."&RIGHT(B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=0;B2;"YOK")))));B2;IF(SEARCH(",";B2;1)=3;LEFT(B2;2)&"."&RIGHT( B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=2;LEFT(B2;1)&"."&RIGHT(B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=4;LEFT(B2;3)&"."&RIGHT(B2;LEN(B2)-SEARCH(",";B2;1));IF(SEARCH(",";B2;1)=0;B2;"YOK")))))&" "&VALUE(RIGHT(F2;3))

Originally Posted by DonkeyOte

And just out of curiosity - re: C1 could you not use:

C1: =TEXT(M1;"ddmmyy")&" "&N1&" "&LEFT(F1;4)&" "&SUBSTITUTE(B1;",";".")&" "&VALUE(RIGHT(F1;3))

Regardless I would be surprised if these formulae were to cause significant slowdown, do you have other formulas running on the sheet ... in particular any Volatiles (INDIRECT,OFFSET etc..) and/or Arrays (incl. Sumproducts)

Hi Donkeyote

I have many formulas like this and sometimes it slows like it will never end. Could i adjust a macro instead of this formula, it will be so thankful. I can not use manual calculation because sometimes i may be forget it to apply , errors was occurred in my past practice.