Hi Rob,

Thanks for your response, it worked but for the "ˆš" symbol of course. Is
there a table of characters from which to choose that corresponds to the
CHAR( )? Or is there a way to define a font and character code in RTF?

"okrob" wrote:

On May 2, 1:47 pm, Pierre M wrote:
I have a macro in which I have a formula which subsitutes symbols for numbers
but VBA turns all the symbols to "?". The formula is as follows:

=IF(I4=1,SUBSTITUTE(I4,1,"ˆš",1),IF(I4=2,SUBSTITU TE(I4,2,"?",1),IF(I4=3,SUBSÂ*TITUTE(I4,3,"—�",1), IF(I4=0,SUBSTITUTE(I4,0,"—‹",1)))))

How can I gat VBA to use the ˆš —� and —‹ symbols in the formula?

You could use the character code...
=IF(I4=1,SUBSTITUTE(I4,1,CHAR(0),1),IF(I4=2,SUBSTI TUTE(I4,2,CHAR(63),
1),IF(I4=3,SUBSTITUTE(I4,3,CHAR(7),
1),IF(I4=0,SUBSTITUTE(I4,0,CHAR(176),1)))))
The exception is CHAR(0) for the first substitution. I'm not sure off
hand what character the sqrt symbol is. It may be that you have to
find somewhere to put the symbols (on another sheet or something) then
refer to the corresponding cell in your formula. I put the symbols
into A1, A2, A3, and A4 on the same sheet. Then wrote my formula like
this:

=IF(I4=1,SUBSTITUTE(I4,1,A11),IF(I4=2,SUBSTITUTE(I 4,2,A2,1),IF(I4=3,SUBSTITUTE(I4,3,A3,1),IF(I4=0,SU BSTITUTE(I4,0,A4,1)))))

It worked and you can definately write that from VBA.

Hope this helps...
Rob