Excel Formula Display in Cell

Solved/Closed
eltonlaw Posts 7 Registration date Saturday April 4, 2009 Status Member Last seen April 8, 2009 - Apr 8, 2009 at 01:42 AM
 rc - Mar 25, 2015 at 05:55 PM
Hello,
Apologize if the question is too stupid.
In cell A4, it contains a formula =Sum(A1:B3)+A3/B2.
How to display the formula in cell A6 for this formula please ?
That is =Sum(A1:B3)+A3/B2
I know Control + ~ can view the formula in a spreadsheet.
But that is view only. Another control + ~ will turn back to normal.
I want to display the formula in cell.
Thanks

3 replies

aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Apr 8, 2009 at 09:27 AM
Hi,

Try with this macro :

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


Hope this will work as you want.

Best regards
14
Hey,

https://ccm.net/forum/affich-85224-excel-formula-display-in-cell

do you know how to do this for whole range of formulas. Let's say the values are in column A and I want to see all the formulas in column B
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491 > edvin
Feb 17, 2010 at 02:30 PM
Hello,

Create a personalized macro function in a new module :

Function DisplayFormula(cel As Range)
        DisplayFormula = cel.Formula & ""
End Function


and after use this new function to the column B cells.
For example, if you want to display the A2 formula in B2, you have to write in B2 the following formula :
=DisplayFormula(A2)

Best regards
0
Excellent... Created and ran macro, pressed F2... Formula is gone... Thank you
0
eltonlaw Posts 7 Registration date Saturday April 4, 2009 Status Member Last seen April 8, 2009 8
Apr 8, 2009 at 09:55 PM
Thanks.
It worked !!!
5
eltonlaw Posts 7 Registration date Saturday April 4, 2009 Status Member Last seen April 8, 2009 8
Apr 8, 2009 at 04:18 AM
Hello,

Add more .........

Apologize if the question is too stupid.
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 please ?
That is ... I want cell A6 to display =Sum(A1:B3)+A3/B2
I know Control + ~ can view this formula in a spreadsheet.
But that is view only. Another control + ~ will turn back to normal.
Also, put ' at the front and paste the formula can help 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"
Next time the formula in cell 6 may be =A3-B2+A1 (not =SUM(A1:B3)+A3/B2)
Use Marcos to run will become overwrite the new formula to =SUM(A1:B3)+A3/B2 instead of =A3-B2+A1
Someone asked me to use Word as interface and then copy/paste as value.
But I want to use the functions within Excel or use Marcos to accomplish changing cell formula to text.

Can any expert show me how to solve please ?
Thanks
3