importing formulas

[joebars]

I would like to know if there is any way to force excel to recognize an imported cell as a formula. When I import a text file containing a cell beginning with = excel adds a ' in front of the = to convert it to a text cell.

 

[Damon Ostrander]

Hi joebars,

Here is a macro that will convert all text strings on the active worksheet that start with "=" to formulas:

Sub TextToFormulas()
'changes any cells on the active worksheet that contain text
'that starts with "=" to a formula. This enables formulas to
'be imported from a file as text.
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Not Cell.HasFormula And Left(Cell.Value, 1) = "=" Then
Cell.Formula = Cell.Value
End If
Next Cell
End Sub

This macro should be placed in a standard macro module (Alt-TMV, Alt-IM, paste code into VBE code pane).

 

[joebars]

Thanks Damon. I'm still hoping to find a lead character (similar to the ' that excel places in the cell) that I can place in the text file so that the formula will work when the sheet is opened (I'm actually exporting the text file from another app that saves it to an excel format). In the meantime, the macro will help.

 

[Damon Ostrander]

Hi again joebars,

It is possible to read the file setting the formulas at the same time if you do it using VBA I/O rather than importing it as a text file. By VBA I/O I mean writing an import macro that reads the data and writes it into the cells as values if no leading equal sign and as formulas otherwise. If you want to investigate this you can read up on it in the VBA helps. See the keywords Open, EOF, Close, and Input #.

Damon