Cell displays formula instead of the formula's result

[Bob]

When copying an Excel-constructed formula from another application (Business
Objects) and pasting it into Excel, the cell displays the formula instead of
the formula's result.

The Excel-constructed formula is:
=HYPERLINK("http://www.microsoft.com","Microsoft")

The format of the cell is set to "General", and there are no strange or
extraneous characters either in front of, or at the end of, the formula.

Yet, after I have pasted the formula into Excel, if I then press the F2 key
and then the Enter key, the result of the formula is correctly displayed
(including the hyperlink)!

Does anyone know what is causing this behavior, and how to solve this
problem so that the pasted formula displays correctly? Any help would be
greatly appreciated.

Thanks,
Bob

 

[Dave Peterson]

My first guess is that the numberformat is changed to General after the formula
has been entered. And changing the numberformat isn't enough to make excel
change the value in that cell.

Any chance???

 

[Bob]

Dave,
Thanks for the suggestion, but I have already verified that the cell format
is set to "General" prior to, and after, the formula is pasted.
Bob

 

[Dave Peterson]

How are you assigning that formula?

Any chance that there's leading spaces?

Or using .value instead of .formula (although excel's VBA seems to forgive me
when I do it).

If worse comes to worst, maybe you can add another step to your code.

In VBA:
with worksheets("Sheet9999")
.cells.replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False)
end with

It's just replacing an equal sign with an equal sign.