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

Bono330

New Member
Joined
Apr 3, 2011
Messages
10
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:p></o:p>
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:p></u1:p><o:p></o:p>

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

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,523
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

New Member
Joined
Apr 3, 2011
Messages
10
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:p> </o:p>
I join them in destination cell ("h2") with "=" sign using:
Range("h2").Formula = "="<o:p></o:p>
Range("h2").Formula = Range("h2").Formula & _<o:p></o:p>
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula
<o:p> </o:p>
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:p></o:p>
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,523
The cell must be formatted as something other than text before you put your formula into it. And this code must be altered from:
Code:
Range("h2").Formula = "="
Range("h2").Formula = Range("h2").Formula & _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula

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

Bono330

New Member
Joined
Apr 3, 2011
Messages
10
The cell must be formatted as something other than text before you put your formula into it. And this code must be altered from:
Code:
Range("h2").Formula = "="
Range("h2").Formula = Range("h2").Formula & _
Workbooks("Makro do Umsatz-3.xls").Worksheets(a).Range("I1").Formula
to be:
Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top