[Excel]changing cell formula to text

Last update on November 4, 2009 09:26 AM by aakai1056
Published by aakai1056

[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.
Best answers for « changing cell formula to text » in :
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Personalize web pages with GreaseMonkey ShowPersonalize web pages with GreaseMonkey What is GreaseMonkey? Example 1 Example 2 XPath Utilities removeElement() removeAttributeOfElement() setAttributeOfElement() injectCSS() Links What is GreaseMonkey? GreaseMonkey is an...
Spreadsheet - Text Operators ShowConcatenation Operator Spreadsheets generally use an oeprator, called a concatenation operator , that allows two text values to be added together. The concatenation operator, written as &, is used as follows: In the above example, the cell...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
They need your help