Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
Hello,
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
Consider the following User Defined Function (UDF):
Function SeeFormula(r As Range) As String SeeFormula = r.Formula End Function So if cell A1 contains: =1+2 =SeeFormula(A1) in another cell will display the formula in A1 and not the result! User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the UDF from Excel: =SeeFormula(A1) To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm or http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for specifics on UDFs -- Gary''s Student - gsnu200844 "Elton Law" wrote: Hello, 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
Thanks Gary ....
That is great .... I make it .... But there is one more stupid question. How to put this UDF in Excel and apply to all the workbooks I open please ? I mean makie it built-in to Excel and I use every time I like. Thanks so much !!!!! "Gary''s Student" wrote: Consider the following User Defined Function (UDF): Function SeeFormula(r As Range) As String SeeFormula = r.Formula End Function So if cell A1 contains: =1+2 =SeeFormula(A1) in another cell will display the formula in A1 and not the result! User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the UDF from Excel: =SeeFormula(A1) To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm or http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for specifics on UDFs -- Gary''s Student - gsnu200844 "Elton Law" wrote: Hello, 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
You can save it as an add-in and install it whenever you want.
or Put the file in your XLSTART folder, so it will always open and be available. -- Gary''s Student - gsnu200844 "Elton Law" wrote: Thanks Gary .... That is great .... I make it .... But there is one more stupid question. How to put this UDF in Excel and apply to all the workbooks I open please ? I mean makie it built-in to Excel and I use every time I like. Thanks so much !!!!! "Gary''s Student" wrote: Consider the following User Defined Function (UDF): Function SeeFormula(r As Range) As String SeeFormula = r.Formula End Function So if cell A1 contains: =1+2 =SeeFormula(A1) in another cell will display the formula in A1 and not the result! User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the UDF from Excel: =SeeFormula(A1) To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm or http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for specifics on UDFs -- Gary''s Student - gsnu200844 "Elton Law" wrote: Hello, 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
You are great great great great great great great great
All Solved .................. Thanks a lot .... "Gary''s Student" wrote: You can save it as an add-in and install it whenever you want. or Put the file in your XLSTART folder, so it will always open and be available. -- Gary''s Student - gsnu200844 "Elton Law" wrote: Thanks Gary .... That is great .... I make it .... But there is one more stupid question. How to put this UDF in Excel and apply to all the workbooks I open please ? I mean makie it built-in to Excel and I use every time I like. Thanks so much !!!!! "Gary''s Student" wrote: Consider the following User Defined Function (UDF): Function SeeFormula(r As Range) As String SeeFormula = r.Formula End Function So if cell A1 contains: =1+2 =SeeFormula(A1) in another cell will display the formula in A1 and not the result! User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the UDF from Excel: =SeeFormula(A1) To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm or http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for specifics on UDFs -- Gary''s Student - gsnu200844 "Elton Law" wrote: Hello, 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Formula to Text
Hi Gary,
Please let me ask one more question .... I tried this one. It is (1+2+3)/6 = 1 1 2 3 6 1 =(A1+A2+A3)/A4 Formula display correctly too. Thanks for your great help. A5 is showing 1 as anwer. A6 is showing the formula (you taught me. Thanks again). Can cell A7 show the answer as A5 ? Using =A5 in A7, A7 will become 1. I want to display the answer in address (cell reference). Is it feasible ? Thanks "Gary''s Student" wrote: You can save it as an add-in and install it whenever you want. or Put the file in your XLSTART folder, so it will always open and be available. -- Gary''s Student - gsnu200844 "Elton Law" wrote: Thanks Gary .... That is great .... I make it .... But there is one more stupid question. How to put this UDF in Excel and apply to all the workbooks I open please ? I mean makie it built-in to Excel and I use every time I like. Thanks so much !!!!! "Gary''s Student" wrote: Consider the following User Defined Function (UDF): Function SeeFormula(r As Range) As String SeeFormula = r.Formula End Function So if cell A1 contains: =1+2 =SeeFormula(A1) in another cell will display the formula in A1 and not the result! User Defined Functions (UDFs) are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To use the UDF from Excel: =SeeFormula(A1) To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm or http://www.cpearson.com/excel/Writin...ionsInVBA.aspx for specifics on UDFs -- Gary''s Student - gsnu200844 "Elton Law" wrote: Hello, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to extract text out of a paragraph | Excel Worksheet Functions | |||
Help extract numerous text files and how to use avg formula | Excel Worksheet Functions | |||
Formula to extract digits from a text string? | Excel Worksheet Functions | |||
how to extract text from a formula | Excel Discussion (Misc queries) | |||
Can I extract text as a value from a formula? | Excel Worksheet Functions |