0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Changing cell formula to text



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.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Excel - Changing cell formula to text », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!