Replace question

[IvenBach]

I have a macro that will go through and check for Bad/Unwanted data or characters and replaces them with Good. I have come across an instance with the character ⅜ - CODE says its 63 which is a Question Mark ( ? ). Took me a while to figure it out as when I ran my code and it changed everything to the ( -3/8 ). I have done this with success for ¼, ½, ¾, ©, ®, etc... At a loss and hoping to get some guidance or suggestions on this.

This is how I have my code laid out. Meant to compile all my arrays into 1 looping through instead of copy+pasting.

array1 = Array("⅜")
array[B]...[/B]

arrayreplace = Array("-3/8", [B]...[/B] )
arrayCOMPILE = Array(array1, array[B]...[/B])

For HOSTARRAY = LBound(arrayCOMPILE) To UBound(arrayCOMPILE)
        For lngDATA = LBound(arrayCOMPILE(HOSTARRAY)) To UBound(arrayCOMPILE(HOSTARRAY))
            strpass = arrayCOMPILE(HOSTARRAY)(lngDATA)
            FindHiLight strpass
            Selection.Replace what:=arrayCOMPILE(HOSTARRAY)(lngDATA), Replacement:=arrayreplace(HOSTARRAY), _
                LookAt:=xlPart, SearchOrder:=xlByRows
        Next
Next

 

[AlphaFrog]

I'm not sure I understand your question. If you are trying to replace the question mark character, you have to precede it with a Tilde character

~?

The question mark character is used as a wildcard in the Find\Replace function. Preceding it with a tilde designates that you want to replace a literal question mark.

 

[IvenBach]

Replace: "⅜" - With: "-3/8"

I used =CODE("⅜") and it came up with 63, whereas 63 is a question mark. When I initially ran the macro (as expected with a question mark - ? - It replaced everything with -3/8. I would like to know how to figure out what code the ⅜ symbol is using and use that within my macro code to properly remove it. It is unicode character 215C and I don't know how to deal with unicode information yet. Anyone able to direct me down the right path?

 

[Scott Huish]

This would be very simple, if you just looped through the array using Replace on the spreadsheet. It is ChrW(&H215C)

Sub test()
Dim replacewhat, replacewith, x As Long
replacewhat = Array(ChrW(&H215C))
replacewith = Array("-3/8")
For x = LBound(replacewhat) To UBound(replacewith)
    Range("A:A").Replace replacewhat(x), replacewith(x)
Next x
End Sub

 

[IvenBach]

Thank you HOTPEPPER. Works perfectly.

In the sample code that you posted you list it as &H215C. Why, if you don't mind me asking, do you put the &H in front? I tried removing it but came up with Syntax Error or Compile Error. Is there anywhere I can read up on this to learn more?

 

[Scott Huish]

&H indicates that the number to follow is in hexadecimal.

In decimal it would be ChrW(8540)

 

[IvenBach]

Thanks again. I'll try and educate myself on hexadecimal to better understand when it comes up.