Hi,

Change the range to suit and try this

Sub changetoformula()
Set myrange = Range("A1:a100")
For Each c In myrange
If c.HasFormula = False Then
c.Formula = c.Value
End If
Next
End Sub

Mike

"BlueD" wrote:

Hi,

I have a MS Access DB that export an excel sheet and in one of the
columns it has a formula stored as string with the little " ' " in
front of the " = " sign.

To convert it manually I select the range and run the text to columns.
But when I try it in a macro it doesn't work.

I have tried a code that looks like this:

For Each c In Range("J2:K5")
c.Formula = c.Value
Next c

or even

For Each c In Range("J2:K5")
c.Formula = Right(c.Value, -1 + Len(c.Value))
Next c


being that the value of the cell is printed in the debug as:
=SE(ÉNÚM(LOCALIZAR("#";BLP("c Equity";"ID_SEDOL1")));"";BLP("c
Equity";"ID_SEDOL1"))

when I select the same cell on excel I see:
'=SE(ÉNÚM(LOCALIZAR("#";BLP("c Equity";"ID_SEDOL1")));"";BLP("c
Equity";"ID_SEDOL1"))

in the first case I keep with the same formula stored as string.
In the second case I crop part of my formula, but it keeps it as
string.


Does any one have a clue on how to work around.

thank you,