Excel Formula Display in Cell [Solved/Closed]

Report
Posts
7
Registration date
Saturday April 4, 2009
Status
Member
Last seen
April 8, 2009
-
 rc -
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

Posts
7077
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 18, 2020
498
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2784 users have said thank you to us this month

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
Posts
7077
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 18, 2020
498 > edvin
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
Excellent... Created and ran macro, pressed F2... Formula is gone... Thank you
Posts
7
Registration date
Saturday April 4, 2009
Status
Member
Last seen
April 8, 2009
8
Thanks.
It worked !!!
Posts
7
Registration date
Saturday April 4, 2009
Status
Member
Last seen
April 8, 2009
8
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