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,SUBSTITUTE(I4,3,"—�",1),IF (I4=0,SUBSTITUTE(I4,0,"—‹",1)))))

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

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

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

On May 3, 8:59Â*am, Pierre M wrote:
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,SUBSTITÂ*UTE(I4,3,A3,1),IF(I4=0, SUBSTITUTE(I4,0,A4,1)))))

It worked and you can definately write that from VBA.

Hope this helps...
Rob- Hide quoted text -

- Show quoted text -

Actually, after trying it several times, I couldn't find a way to
bring that character up. It's not part of the 255 characters that
CHAR() refers to. I found the best way was just putting the character
into a separate cell, and refering to it as with my second solution.
Also, the characters for —‹ and —� weren't the right ones either. As I
looked into it a little further, they aren't one of the 255.
Someone else may be able to help more, but again, the second solution
will work.

Thanks Rob, I got it to work with a combination of "CHAR" commands and
grabbing the symbol from another sheet as you suggested but I had to use a
"Sheet2!R1C1" format for the location of the symbol's cell, just using "A1"
for example did not work. BTW CHAR(7) and CHAR(176) did display —� and —‹
correctly in our environment. I have noticed a few other differences in our
VBA configuration. Maybe a different version?

Anyway, thanks again.

Pierre

"okrob" wrote:

On May 3, 8:59 am, Pierre M wrote:
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,SUBSTITÂ*UTE(I4,3,A3,1),IF(I4=0, SUBSTITUTE(I4,0,A4,1)))))

It worked and you can definately write that from VBA.

Hope this helps...
Rob- Hide quoted text -

- Show quoted text -

Actually, after trying it several times, I couldn't find a way to
bring that character up. It's not part of the 255 characters that
CHAR() refers to. I found the best way was just putting the character
into a separate cell, and refering to it as with my second solution.
Also, the characters for —‹ and —� weren't the right ones either. As I
looked into it a little further, they aren't one of the 255.
Someone else may be able to help more, but again, the second solution
will work.