CODEWITHSUNDEEP
excelexcel-2003vba
Liam Shaw
Liam Shaw

Reputation:

How do I replace the formula in a cell with the output of the formula?

How do I replace the formula in a cell with the output of the formula?

I simply need "=RIGHT(E86,LEN(E86)+1-FIND("(",E86,1))" to become "(e)"

Is there a way to do this to the whole sheet? Replace all the cells with formulas with the text they are displaying? I am using version 2003.

Upvotes: 0

Views: 1189

Answers (3)

Cypher2100
Cypher2100

Reputation: 331

Select the cells you want to replace and copy them. Then go to "Edit->Paste Special" and select values instead of all.

Since this doesn't change non-formula cells, you could select the entire sheet and copy->paste special to remove all formulas.

To do it programatically, look at Steve's answer. He's got the code you'll need.

Upvotes: 8

Steve Homer
Steve Homer

Reputation: 3922

Alternatively something like the following will work if you want to avoid using the clipboard


Dim r as range
For each r in Worksheets("Sheet1").UsedRange.Cells
   r.Value = r.Value
Next

I haven't excel to hand I'm afraid so you'll need to check the syntax.

Upvotes: 2

devuxer
devuxer

Reputation: 42344

Another way to do it: double-click the cell in question and press F9.

Upvotes: 1

Related Questions