Excel - Changing cell formula to text

December 2016



Issue



Consider that I have:
  • In cell A4, it contains a formula =Sum(A1:B3)+A3/B2.
  • How to extract this formula in cell A6 as a string of text?
    • That is ... I want cell A6 to display =Sum(A1:B3)+A3/B2
    • Using Control + ~ I can view this formula in a spreadsheet.
    • But it is in view only mode. Pressing Control + ~ will turn back to normal.
    • Also, I've tried to put ' at the front and paste the formula in one cell ... but this is not applicable in VBA (marco writing).
    • VBA will capture the script as below

ActiveCell.FormulaR1C1 = "'=SUM(A1:B3)+A3/B2"  



*
    • Note that the formula in cell 6 may be =A3-B2+A1 (not =SUM(A1:B3)+A3/B2)
    • Using Marcos to run it will overwrite the new formula to =SUM(A1:B3)+A3/B2 instead of =A3-B2+A1
    • How to use the functions within Excel or use Marcos to accomplish changing cell formula to text.

Solution


Try with this macro :

Sub DysplayFormula() 
    Range("A6").Value = "'" & Range("A4").Formula & "" 
End Sub 

Notes


Thanks to aquarelle,for this tip.

Related :

This document entitled « Excel - Changing cell formula to text » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.