Convert Text into Formula in VBA
 

How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance

Convert Text into Formula in VBA
 

Hi AK,

Your question is not clear.starting with the text:

18.5x20x15

what is the deired result?


---
Regards,
Norman


"ak" wrote in message
...
How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance

Convert Text into Formula in VBA
 

This works for your example. You should add error checking and ensure that
you are replacing the correct chars, but you get the idea.

Public Function MyEval(argCell As Range) As Variant
Dim TempStr As String

TempStr = Replace(argCell.Text, "x", "*")

MyEval = Evaluate(TempStr)

End Function

NickHK

"ak" wrote in message
...
How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance

Convert Text into Formula in VBA
 

Hi Norman,
I would like to have the text become a formula:-

=18.5*20*15

actually this kind of text also exist in the cell
18.5*20*15

then how can this change to the formula?
=18.5*20*15

Finally the value =5550
"Norman Jones" wrote:

Hi AK,

Your question is not clear.starting with the text:

18.5x20x15

what is the deired result?


---
Regards,
Norman


"ak" wrote in message
...
How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance

Convert Text into Formula in VBA
 

hi, ak !

How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-
18.5x20x15
instead of using the INSTR to find each "x" , any other method?
thanks for the help in advance

i.e.: - cells(1,1)="="&replace(cells(1,1),"x","*")

or, if you need for excel 97: - cells(1,1)="="&application.substitute(cells(1,1)," x","*")

hth,
hector.

Convert Text into Formula in VBA
 

Hi AK,

Hector's suggestion will furnish a formula either for text
of the type 18.5x20x15 or 18.5*20*15.

Nick's function will return the formula value in either case.


---
Regards,
Norman

"ak" wrote in message
...
Hi Norman,
I would like to have the text become a formula:-

=18.5*20*15

actually this kind of text also exist in the cell
18.5*20*15

then how can this change to the formula?
=18.5*20*15

Finally the value =5550

Convert Text into Formula in VBA
 

I have a related question about converting text into a formula. I'm
developing some spreadsheets for student use. I would like the students to
be able to type in an expression for a function as text -- say something like
x^3-5*x^2+3 -- and then have the spreadsheet convert that text into an active
formula that can be used. By the way, the x would refer to a named cell,
which would then be used in a data table to create a full table.

If anyone can give me some suggestions, I would be most appreciative. Many
thanks for looking.

"ak" wrote:

How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance