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?

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?

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?

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?

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?

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?

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?

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?