Is this possible in Excel?

[Jan Nademlejnsky]

Is this possible in Excel: I want to write formula in A column so it is
visible and then it would be converted into real formula in B column.

Example:

at A7 'A2 + G17 =
at B7 +A2+G17

I used combinations of CONCATENATE() and Value string manipulations, but
nothing works.
Appreciate your help.

Jan

[Chip Pearson]

Jan,

Not without using a VBA function. Try

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then, call this as =Eval(A7) in cell B7.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Jan Nademlejnsky" <jan...@home.com> wrote in message
news:OUWGRwA...@cppssbbsa02.microsoft.com...

[Patrick Molloy]

Right click the sheet tab & select View Code, which will open the sheet's
code page, then paste in this sub...

Private Sub Worksheet_Change(ByVal Target As Range)
on error goto trap
If Target.Address = "$A$1" Then
Range("B1").Formula = "=" & Target
End If
exit sub
Trap:
Range("B1")=""
End Sub

It checks the address of the Target cell - thats the cell that you just
enetered a value in. If it is cell A1 then whatever is entered in A1 becomes
the formula for B1

[Jan Nademlejnsky]

It was very fast response. Thank you very much

I will need more help with this one, because I am not in your level of
knowledge. Can you show me this specifically for the following example,
please:

I have at a7 'A2 + G17 =
I want to have in b7 +A2+G17

What is Range and what is Text in my example?

Thanks

Jan

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:OHHg$4AIAH...@cppssbbsa02.microsoft.com...

> Jan,
>
> Not without using a VBA function. Try
>
> Function Eval(Rng As Range) As Variant
> Application.Volatile True
> Eval = Evaluate(Rng.Text)
> End Function
>
> Then, call this as =Eval(A7) in cell B7.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com ch...@cpearson.com
>
>
>

[Chip Pearson]

Jan,

If cell A7 contains
'A2 + G17 =

the VBA function will have to trim off the equals sign. Therefore, the
function needs to be rewritten as

Function Eval(Rng As Range) As Variant

Dim TempString As String
Application.Volatile True
TempString = Trim(Rng.Text)
If Right(TempString, 1) = "=" Then
TempString = Left(TempString, Len(TempString) - 1)
End If
Eval = Evaluate(TempString)
End Function

Then, you call the function from cell B7 by entering the following formula
in B7.

=Eval(A7)

This passes A7 the the function Eval. The Eval function reads the text in
A7 (which is A2 + G17 = ) and removes the trailing equals sign, and passes
the result to Excel's Evaluate function. (You can't call Evaluate directly
from a worksheet cell, only from VBA.)

This places the result of the formula in A7 into B7 -- it doesn't not
convert A7 to a real formula, and then put that formula into a B7. In other
words, your worksheet will not contain the formula =A2+G17.

If you want to do this, you'll need a Sub procedure, not a Function. Try
something like

Sub MakeFormula()
Dim Rng As Range
Dim TempString As String
For Each Rng In Selection
TempString = Trim(Rng.Text)
If Right(TempString, 1) = "=" Then
TempString = Left(TempString, Len(TempString) - 1)
End If
TempString = "=" & TempString
Rng(1, 2).Formula = TempString
Next Rng

End Sub

Now, select the cells containing the "text formulas". Then run the
MakeFormula macro. It will place the "real" formula in the column to the
right of the "text" formula. For example, if A1, A2, and A3 have the
following text formulas,

'C1+C2 =
'C2+C3 =
'C3+C4 =

select A1:A3, and run the macro. It will place the formulas

=C1+C2
=C2+C3
=C3+C4

in cells B1:B3.

I hope this clears things up. Post back if you need any more help.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Jan Nademlejnsky" <jan...@home.com> wrote in message

news:OfQGPFB...@cppssbbsa02.microsoft.com...

[Jan Nademlejnsky]

This is excellent. This applies only to one cell, but now, I want this to
apply to whole range of cells. Let's say that the displayed formulae are in
column A and calculation formulas in column B. Can you help me with this,
please.

Thanks

Jan

"Patrick Molloy" <patrick...@hotmail.com> wrote in message
news:u7b$#7AIAHA.289@cppssbbsa04...

> Right click the sheet tab & select View Code, which will open the sheet's
> code page, then paste in this sub...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> on error goto trap
> If Target.Address = "$A$1" Then
> Range("B1").Formula = "=" & Target
> End If
> exit sub
> Trap:
> Range("B1")=""
> End Sub
>
> It checks the address of the Target cell - thats the cell that you just
> enetered a value in. If it is cell A1 then whatever is entered in A1
becomes
> the formula for B1
>
>

[Jan Nademlejnsky]

Thank you very much. I think this will do it. I will try it tonight.

Jan

"Chip Pearson" <ch...@cpearson.com> wrote in message

news:ONHKaQB...@cppssbbsa02.microsoft.com...

> "Jan Nademlejnsky" <jan...@home.com> wrote in message

[Patrick Molloy]

two answers: bot assume formula text starts in A1, and the resulting
formula/answer will be in B1

Answer #1

Private Sub AddFormula()
on error goto trap
rw =1
do until cells(rw,1)=""
cells(rw,2).formula = "=" & cells(rw,1)
rw =rw+1
loop

exit sub
Trap:
Range("B1")=""
End Sub

Answer #2
in B1 type
=Eval(A1) 'see Chip Pearson's reply

replicate down column B

"Jan Nademlejnsky" <jan...@home.com> wrote in message

news:u0EqGTB...@cppssbbsa02.microsoft.com...