| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting text to formulas
I am trying to streamline a mathematical optimization program that I wrote in
Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
try using INDIRECT()
Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
I tried using that, but it still only returns a text string, at least with
the way that I am using it. "Tieske" wrote: try using INDIRECT() Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
you're correct. INDIRECT only resolves a cell/range reference, not an entire
formula. Ok, via VBA then. Try this macro: Sub CalcFormula() Dim myFormula As String myFormula = Range("A4").Value Range("C4").Formula = "=" & myFormula End Sub Create a button and hook it up to the macro. A4 contains your formula in "plain text", C4 will have the calculated result. Tieske "Eutrapelia" wrote in message ... I tried using that, but it still only returns a text string, at least with the way that I am using it. "Tieske" wrote: try using INDIRECT() Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
See Evaluate Method in Excel Help!
Regards, Stefi €žEutrapelia€ť ezt Ă*rta: I tried using that, but it still only returns a text string, at least with the way that I am using it. "Tieske" wrote: try using INDIRECT() Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
yes! thats the one I meant. This should do it
"Stefi" wrote in message ... See Evaluate Method in Excel Help! Regards, Stefi "Eutrapelia" ezt írta: I tried using that, but it still only returns a text string, at least with the way that I am using it. "Tieske" wrote: try using INDIRECT() Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
Thanks,
I was able to get it to work. It was a bit cumbersome, since I had to use the Evaluate the function every time that the values changed, but it worked well. Jeremy "Stefi" wrote: See Evaluate Method in Excel Help! Regards, Stefi €žEutrapelia€ť ezt Ă*rta: I tried using that, but it still only returns a text string, at least with the way that I am using it. "Tieske" wrote: try using INDIRECT() Tieske "Eutrapelia" wrote in message ... I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting text to formulas
Can you use a UDF?
Function EvalCell(RefCell As String) EvalCell = Evaluate(RefCell) End Function =EvalCell(A1) returns 2576 Gord Dibben MS Excel MVP On Thu, 28 Sep 2006 22:13:01 -0700, Eutrapelia wrote: I am trying to streamline a mathematical optimization program that I wrote in Excel with a macro. Say that I have typed a formula into cell A1 as a text string, such as "x^2+10*x^4". Next I have renamed cell B5 as "x". I want the cell C5 to convert the formula written as a text in A1 to the formula "=x^2 +10*x^4" which will reference cell B5 and calculate it without having to manually copy in the equation from A1. I tried the Visual Basic Range.Formula= function but this only returns a text. Substitute() and Text() do not seem to work either. Does anyone know how to do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
| How to identify text from a autofiltered list using formulas | Excel Worksheet Functions | |||
| need a refresher: text boxes on charts that use relational formulas? | Charts and Charting in Excel | |||
| show text after formulas | Excel Discussion (Misc queries) | |||
| Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Linear Mode
