Unwanted single quote displayed in Formula bar

[Dave Peterson]

Sent in a private email:

I think it's that pesky ' that you use to make the formula text.

If you run a macro like this:

option explicit
sub testme01()
msgbox range("b5").prefixcharacter
end sub

after you do each step, you'll see that excel will see a prefixcharacter in that
cell.

I pasted from E5 to B5 and ran that code and saw: ' (the apostrophe)
then I removed the apostrophe and saw: (nothing) (the apostrophe was gone)
But when I converted to values and ran that code, the apostrophe was back.

I've been lucky in that I don't use the apostrophe to force my formulas to
text. I use $$$$$.

$$$$$=IF(MID(A5,LEN(A5),1)="q",A5&"ue","")

(or some other non-apostrophe string)

 

[Guest]

I think Dave has come up with the solution.
"I think it's that pesky ' that you use to make the formula text."
Avoiding it seems to be a good idea.
See his last posting.

Haven't read all of this but have you tried

range("a2").formula=range("a2").value