Excel - Changing cell formula to text

Ask a question


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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team