Converting a Formula stored as String to real Formula

[BlueD]

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,

 

[Mike H]

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

 

[Gord Dibben]

Sub Change()
For Each c In Range("J5:K17")
c.NumberFormat = "General"
c.Formula = c.Value
Next c
End Sub


Gord Dibben MS Excel MVP

 

[JP]

How about

Sub fixformulas()

Dim cell As Excel.Range
For Each cell In Selection
cell = "=" & Right(cell, Len(cell) - 1)
Next cell

End Sub

Highlight the offending data and run. Crude but effective.

HTH,
JP