PDA

View Full Version : [SOLVED] How to replace a symbol like ≤ by using VBA?



stranno
11-07-2015, 06:20 AM
I tried to replace the symbol ≤ in a string. Without succes so far. Does someone know how to do this?

Sub test()
ActiveCell.Offset(0, 1) = Replace(ActiveCell.Value, "=", "@")
End Sub

Suppose the ActiveCell contains the string "100 ≤ t ≤ 150"

Now i want to replace the ≤ characters in this string by the symbol @.
How do i do that? The code above fails.

The problem is that you can't type symbols like ≤ in a module.

Stranno

Kenneth Hobs
11-07-2015, 10:15 AM
In cell B1 of this example, I put the value of the symbol to replace in cell A1.

Sub ken()
Range("A1").Replace What:=Range("B1").Value2, Replacement:="@", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

mikerickson
11-07-2015, 10:31 AM
"≤" = Chr(178)
"≥" = Chr(179)
"≠" = Chr(173)

So you could use code like

myString = Replace(myString, Chr(178), "@")

stranno
11-07-2015, 11:23 AM
Thanks a lot guys. At the moment i am not equipped with a computer but i assume this will work. I did try the ascii code but i obviously took the wrong number. I think VBA's answer to msgbox asc(≤) was 61 or something like that.

mikerickson
11-07-2015, 12:45 PM
I got those number from a worksheet using the CODE function.

stranno
11-07-2015, 12:54 PM
Right.

stranno
11-07-2015, 01:24 PM
Unfortunately my reaction was a bit premature. Both solutions don't work on my computer. If i run the following code:
on error resume next
For i = 1 to 1000
cells(i,1) = chr(i)
next

i see no ≤, ≥ or ≠ character at all.

On my computer chr(173), chr(178) and chr(179) represents other characters.

Also Kenneth's solution did not work.

keyboard settings?

mikerickson
11-07-2015, 01:27 PM
Have you tried
ChrW(8804) "≤"
ChrW(8805) "≥"
ChrW(8800) "≠"

stranno
11-07-2015, 01:49 PM
Yes mikerickson i just came to the same solution.

Sub n()
ActiveSheet.Cells(1, 1) = ChrW(&H2264)
MsgBox Replace(ActiveSheet.Cells(1, 1), ChrW(&H2264), "@")
End Sub

with hexadecimals

Kenneth Hobs
11-07-2015, 03:05 PM
My solution does "work" or I would not have posted it.

In any case, the ChrW() method appears to work fine too.

Mike's other code probably works for Mac but not Windows.

mikerickson
11-07-2015, 03:48 PM
Or, it may be specific to the font Calibri, which is my default font.

stranno
11-08-2015, 05:23 AM
My solution does "work" or I would not have posted it.

In any case, the ChrW() method appears to work fine too.

Mike's other code probably works for Mac but not Windows.

Honestly Kenneth, your example does nothing. Cell A1 remains empty and teh value in Cell B1 doesn't change. Even if i put a 'normal' value in B1 (for example the letter H) nothing happens.

Kenneth Hobs
11-08-2015, 06:33 AM
Why would B1 change? That has the Replace character to find for replacement in my method. Of course one can use ChrW() method in my method as well. Replacing by B1 gives you flexibility to change any character(s).

stranno
11-08-2015, 08:35 AM
Kenneth, I am ashamed. I have totally misunderstood your answer. Thanks a lot for your efforts to convince me.
stranno