Formulas stored as text

[sraasch]

I'm trying to copy text-formatted cells containing formulas from a "template" sheet to another sheet where the formulas will be calculated.

I'm storing the formulas as text because I've been unable to stop Excel 2010 from corrupting the formulas (it seems to replace some of the formulas with "#Name" text). Changing the cells to text formatting fixes this.

Due to the large number of cells I'm copying and that I want to retain formatting from the template sheet, I'm using Range.copy to copy the formulas.

I've seen and tried a technique that uses TextToColumns after the copy operation, and this seems to work. I'm hoping for a "prettier" solution.

I understand that using the Evaluate macro may help with this, but I'm concerned about speed of operation during the COPY phase as well as the during the calculation.

Thanks for reading.

 

[sraasch]

<grin>

I agree... it's complex, and I may not have done a good job explaining the "big picture".

As I said in the original post, I'm looking for a way to convert a formula in a text-formatted cell into a real formula in another, without resorting to cell-by-cell copying.

Thanks for your time!

 

[Joe4]

Let's say that you have the "Text" formula expression in cell C1, and want to make that an actual formula in cell D1. This code works for me:

    Range("D1").Formula = Range("C1").Text