Replacing 1/3 and 2/3

[Charlotte E]

Hey,


I have a VBA code, which return the result into a worksheet cell, in the
form of a test string.

The result string can be one of the following:

- "You need to use 1/4 of the basis"
- "You need to use 1/2 of the basis"
- "You need to use 3/4 of the basis"
- "You need to use 1/3 of the basis"
- "You need to use 2/3 of the basis"

It's the two last ones that causes me problems....


I want the text in the cell to be like this:

- "You need to use ¼ of the basis"
- "You need to use ½ of the basis"
- "You need to use ¾ of the basis"

So I simply use a code line like:

ReturnString = Replace(ReturnString," 1/2 "," ½ ")

And that's no problem with 1/4, 1/2 and 3/4, but when I try to make the
Replace with 1/3 and 2/3, all I get is a questionmark, in the Replace line,
when I try to copy the char for 1/3 into the line???

ReturnString = Replace(ReturnString," 1/3 "," ? ")

How to do it?


TIA,

 

[Patrick Molloy]

the 1/2, 1/4 etc are characters in the set, such as Courier, that you're
using in the sheet for your text. There is no sysmbol for the ?/3rds. In
Excel, you can check using Insert/Symbol and looking at the characters
available to you

 

[Charlotte E]

the 1/2, 1/4 etc are characters in the set, such as Courier, that
you're using in the sheet for your text. There is no sysmbol for the
?/3rds. In Excel, you can check using Insert/Symbol and looking at
the characters available to you

Strange, 'cause when I look in the Windows "Character Map" utility, I can
see the 1/3 and 2/3 signs, and I can also manually copy those signs into my
Excel spreadsheet and use them in there.

In fact, I can even do what I want in a standard Excel function:

=SUBSTITUTE(A1;" 1/3 ";" ? ")

So, it really seems strange to me, if I can't do it in VBA???

Anyone?

 

[Patrick Molloy]

did i misunderstand? are you looking for the single character '1/3' or are
you trying to replace the three characters in 1/3 by a question mark?
I undestood the first ...ie look for a single character ...and there is none
afaik

 

[Peter T]

Sub test2()
Dim i As Long, w As Long
Dim s As String

s = "my # fraction"
w = 8531

For i = 1 To 12
Cells(i, 1) = w
Cells(i, 2) = Replace(s, "#", ChrW(w))
w = w + 1
Next
End Sub

Regards,
Peter T

 

[Patrick Molloy]

cool. its in the Arial font group. I missed it when i first looked.

 

[Rick Rothstein]

See if you can use this function to translate your fractions...

Function Fraction(F As String) As String
Dim AscValue As Long
Select Case F
Case "1/4"
AscValue = 188
Case "1/3"
AscValue = 8531
Case "1/2"
AscValue = 189
Case "2/3"
AscValue = 8532
Case "3/4"
AscValue = 190
End Select
Fraction = ChrW(AscValue)
End Function

Note that VB and its controls do not support Unicode print out, so if you
print the return value from the function in the Immediate window or to a
UserForm's TextBox, it will probably return a question mark; however,
worksheets do support Unicode, so you can output the results from the
function to a worksheet and it should work correctly (as long as the cell's
font is Unicode). So, something like this should work correctly for you...

Range("A1").Value = "You need to use " & Fraction("1/3") & " of the basis."

 

[Charlotte E]

Thanks, Rick - working

See if you can use this function to translate your fractions...

Function Fraction(F As String) As String
Dim AscValue As Long
Select Case F
Case "1/4"
AscValue = 188
Case "1/3"
AscValue = 8531
Case "1/2"
AscValue = 189
Case "2/3"
AscValue = 8532
Case "3/4"
AscValue = 190
End Select
Fraction = ChrW(AscValue)
End Function

Note that VB and its controls do not support Unicode print out, so if
you print the return value from the function in the Immediate window
or to a UserForm's TextBox, it will probably return a question mark;
however, worksheets do support Unicode, so you can output the results
from the function to a worksheet and it should work correctly (as
long as the cell's font is Unicode). So, something like this should
work correctly for you...
Range("A1").Value = "You need to use " & Fraction("1/3") & " of the
basis."