How to make a formula work – it is taken as text

[Bono330]

Hello, I have a problem with function which is pasted into a destination cell. It is taken as a text, not as a formula.<o></o>
I have found that using “text to columns” (manually) and setting the cell to column data format “general” helps – formula works. But when I try to record the “text to columns” line it does not work the same way. It stays without switching to formula.<u1></u1><o></o>

Or is there a different way to activating the function?<o></o>
<o></o>

 

[GlennUK]

How are you pasting into the cell ... by hand, or by macro? Is the cell formatted as General or Text before you try this operation?

 

[Bono330]

How are you pasting into the cell ... by hand, or by macro? Is the cell formatted as General or Text before you try this operation?

It is actually quite complicated. I have another workbook where I store formulas (but I store them without “=” sign)
<o> </o>
I join them in destination cell ("h2") with "=" sign using:
Range("h2").Formula = "="<o></o>
Range("h2").Formula = Range("h2").Formula & _<o></o>
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula
<o> </o>
Text in Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1") cell is:
IF(AI2=833;VLOOKUP(AH2;'[Makro do Umsatz-3.xls]441215009200EUR'!$E:$F;2;0);VLOOKUP(AI2;'[Makro do Umsatz-3.xls]441215009200EUR'!$D:$F;3;0))

Formula is ok because when I stop the macro, enter the cell "h2" and make manually text to columns or hit enter it works.

<o></o>

 

[GlennUK]

The cell must be formatted as something other than text before you put your formula into it. And this code must be altered from:

Range("h2").Formula = "="
Range("h2").Formula = Range("h2").Formula & _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula

to be:

Range("h2").Formula = "=" &  _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula

 

[Bono330]

The cell must be formatted as something other than text before you put your formula into it. And this code must be altered from:

Range("h2").Formula = "="
Range("h2").Formula = Range("h2").Formula & _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula

to be:

Range("h2").Formula = "=" &  _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula

I have changed the formula accordingly to:
Range("h1").Formula = "=" & Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula
(it only worked when h1 cell formated to "general" format with instruction
Range("H1").Select
Selection.NumberFormat = "@")

But still the cell content is not recognised as formula. I have to select the cell manually and hit enter to make it work.
I tried using text to columns in my vba code before and after the critical instruction but still it does not change anything...

 

[tusharm]

Change the cell format to general *before* entering the formula.

I have changed the formula accordingly to:
Range("h1").Formula = "=" & Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula
(it only worked when h1 cell formated to "general" format with instruction
Range("H1").Select
Selection.NumberFormat = "@")

But still the cell content is not recognised as formula. I have to select the cell manually and hit enter to make it work.
I tried using text to columns in my vba code before and after the critical instruction but still it does not change anything...