Need to know why Macro is stopping

[jenowell]

I have a Macro that was written by someone in my position before me. It is what we call a scrubbing Macro to clean weird characters. It encounters characters that it does not know what to do with them. Is there anyway to add to this Macro something to help me know what it stopped on? Where it stopped? What it did not like?
Here is the Macro and a sample of the data I run it against.
Sub cleanAttributes()


'********************************************************************************
'* This macro will replace special characters with the Agility special
'* character tag.
'* It will also trim off any extra white-space characters at the beginning or
'* end of the text.
'* As written, this macro is expecting the text to be modified to be in Column B and over
'********************************************************************************


Dim lrow, lcol As Long
Dim x, r, c As Long
Dim xCode As Long
Dim origString As String

lrow = Cells(Rows.count, 1).End(xlUp).row
lcol = Cells(1, Columns.count).End(xlToLeft).Column

For x = 2 To lcol
Columns(x).NumberFormat = "@"
Next x

For r = 2 To lrow
For c = 2 To lcol
If Cells(r, c).value <> "" Then
origString = Cells(r, c).Text

origString = Trim(origString)

' Anything less than 32, remove
For I = 1 To 9
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(13) & ChrW$(10), "<special id=""70""/>") '<HR>
origString = Replace$(origString, ChrW$(13), "<special id=""70""/>") '<HR>
origString = Replace$(origString, ChrW$(10), "<special id=""70""/>") '<HR>
origString = Replace$(origString, ChrW$(11), "")
origString = Replace$(origString, ChrW$(12), "")
For I = 14 To 31
origString = Replace$(origString, ChrW$(I), "")
Next I

origString = Replace$(origString, ChrW$(37), "<special id=""15""/>") '%
origString = Replace$(origString, ChrW$(38), "<special id=""64""/>") '&
origString = Replace$(origString, ChrW$(64), "<special id=""65""/>") '@
origString = Replace$(origString, ChrW$(126), "<special id=""22""/>") '~


' 128 to 159, remove
For I = 128 To 159
origString = Replace$(origString, ChrW$(I), "")
Next I

origString = Replace$(origString, ChrW$(160), " ")
origString = Replace$(origString, ChrW$(161), "i")
origString = Replace$(origString, ChrW$(162), "<special id=""68""/>") 'CENT
origString = Replace$(origString, ChrW$(163), "<special id=""78""/>") 'Pound (money)
origString = Replace$(origString, ChrW$(164), "<special id=""81""/>") 'Box
origString = Replace$(origString, ChrW$(165), "<special id=""27""/>") 'Yen
origString = Replace$(origString, ChrW$(167), "<special id=""19""/>") 'Section
origString = Replace$(origString, ChrW$(169), "<special id=""69""/>") 'Copyright
origString = Replace$(origString, ChrW$(172), "")
origString = Replace$(origString, ChrW$(174), "<special id=""18""/>") 'Registered Mark
origString = Replace$(origString, ChrW$(176), "<special id=""74""/>") 'Degree
origString = Replace$(origString, ChrW$(177), "<special id=""17""/>") 'Plus/Minus
origString = Replace$(origString, ChrW$(178), "<style id=""8"">2</style>") 'Square
origString = Replace$(origString, ChrW$(179), "<style id=""8"">3</style>") 'Cube
origString = Replace$(origString, ChrW$(180), "'")
origString = Replace$(origString, ChrW$(181), "<special id=""8""/>") 'Micro
origString = Replace$(origString, ChrW$(182), "<special id=""14""/>") 'Paragraph
origString = Replace$(origString, ChrW$(183), "<special id=""25""/>") 'Bullet
origString = Replace$(origString, ChrW$(185), "")
origString = Replace$(origString, ChrW$(186), "<special id=""230""/>") 'Ellipsis
origString = Replace$(origString, ChrW$(188), "<fraction d=""4"" n=""1""/>") '1/4
origString = Replace$(origString, ChrW$(189), "<fraction d=""2"" n=""1""/>") '1/2
origString = Replace$(origString, ChrW$(190), "<fraction d=""4"" n=""3""/>") '3/4
origString = Replace$(origString, ChrW$(213), "'")
origString = Replace$(origString, ChrW$(214), "<special id=""239""/>") 'O with umlaut
origString = Replace$(origString, ChrW$(215), "<special id=""23""/>") 'Times Symbol
origString = Replace$(origString, ChrW$(216), "<special id=""1""/>") 'Phi, Capital
origString = Replace$(origString, ChrW$(220), "<special id=""26""/>") 'U with Umlaut
origString = Replace$(origString, ChrW$(233), "<special id=""76""/>") 'E-accent
origString = Replace$(origString, ChrW$(247), "<special id=""75""/>") 'Division Sign
origString = Replace$(origString, ChrW$(248), "<special id=""2""/>") 'Phi, lower case
origString = Replace$(origString, ChrW$(934), "<special id=""2""/>") 'Phi, lower case
origString = Replace$(origString, ChrW$(402), "<special id=""219""/>") 'F latin
origString = Replace$(origString, ChrW$(650), "<special id=""11""/>") 'Omega
origString = Replace$(origString, ChrW$(730), "<special id=""74""/>") 'Degree
origString = Replace$(origString, ChrW$(778), "0<special id=""74""/>") 'Degree
origString = Replace$(origString, ChrW$(913), "<special id=""86""/>") 'Alpha
origString = Replace$(origString, ChrW$(915), "<special id=""91""/>") 'Gamma
origString = Replace$(origString, ChrW$(916), "<special id=""83""/>") 'Delta
origString = Replace$(origString, ChrW$(920), "<special id=""93""/>") 'Theta
origString = Replace$(origString, ChrW$(923), "<special id=""92""/>") 'Lambda
origString = Replace$(origString, ChrW$(931), "<special id=""240""/>") 'Sigma
origString = Replace$(origString, ChrW$(937), "<special id=""11""/>") 'Omega
origString = Replace$(origString, ChrW$(945), "<special id=""225""/>") ' Alpha LC
origString = Replace$(origString, ChrW$(946), "<special id=""20""/>") 'Beta
origString = Replace$(origString, ChrW$(947), "<special id=""91""/>") 'Gamma
origString = Replace$(origString, ChrW$(949), "<special id=""226""/>") 'Epsilon LC
origString = Replace$(origString, ChrW$(951), "<special id=""227""/>") 'Eta LC
origString = Replace$(origString, ChrW$(952), "<special id=""93""/>") 'Theta
origString = Replace$(origString, ChrW$(955), "<special id=""92""/>") 'Lambda
origString = Replace$(origString, ChrW$(956), "<special id=""8""/>") 'Micro
origString = Replace$(origString, ChrW$(960), "<special id=""16""/>") 'Pi
origString = Replace$(origString, ChrW$(964), "<special id=""224""/>") 'Tau LC
origString = Replace$(origString, ChrW$(8208), "<special id=""9""/>") 'Minus
origString = Replace$(origString, ChrW$(8211), "<special id=""9""/>") 'Minus
origString = Replace$(origString, ChrW$(8212), "<special id=""7""/>") 'Long Dash
origString = Replace$(origString, ChrW$(8216), "<special id=""13""/>") 'Quote Open Single
origString = Replace$(origString, ChrW$(8217), "<special id=""71""/>") 'Quote Close Single
origString = Replace$(origString, ChrW$(8217), "<special id=""82""/>") 'Apostrophe
origString = Replace$(origString, ChrW$(8220), "<special id=""12""/>") 'Quotes Open Double
origString = Replace$(origString, ChrW$(8221), "<special id=""4""/>") 'Inch Mark
origString = Replace$(origString, ChrW$(8221), "<special id=""67""/>") 'Quotes Close Double
origString = Replace$(origString, ChrW$(8224), "<special id=""72""/>") 'Dagger
origString = Replace$(origString, ChrW$(8225), "<special id=""73""/>") 'Double Dagger
origString = Replace$(origString, ChrW$(8226), "<special id=""25""/>") 'Bullet
origString = Replace$(origString, ChrW$(8230), "<special id=""230""/>") 'Ellipsis
origString = Replace$(origString, ChrW$(8242), "<special id=""77""/>") 'Foot Mark
origString = Replace$(origString, ChrW$(8304), "<special id=""74""/>") 'Degree
origString = Replace$(origString, ChrW$(8482), "<special id=""24""/>") 'Trademark
origString = Replace$(origString, ChrW$(8486), "<special id=""11""/>") 'Omega
origString = Replace$(origString, ChrW$(8594), "<special id=""229""/>") 'Arrow Rt.
origString = Replace$(origString, ChrW$(8709), "<special id=""1""/>") 'Phi Capital
origString = Replace$(origString, ChrW$(8710), "<special id=""90""/>") 'Differenetial
origString = Replace$(origString, ChrW$(8725), "<special id=""223""/>") 'Fraction Slash
origString = Replace$(origString, ChrW$(8730), "<special id=""21""/>") 'Square-root
origString = Replace$(origString, ChrW$(8734), "<special id=""3""/>") 'Infinity
origString = Replace$(origString, ChrW$(8776), "<special id=""63""/>") 'Approx. Equal
origString = Replace$(origString, ChrW$(8800), "<special id=""10""/>") 'Not Equal
origString = Replace$(origString, ChrW$(8804), "<special id=""5""/>") 'Less than or equal
origString = Replace$(origString, ChrW$(8805), "<special id=""79""/>") 'Greater than or equal
origString = Replace$(origString, ChrW$(9633), "<special id=""81""/>") 'Box
origString = Replace$(origString, ChrW$(9642), "<special id=""234""/>") 'Square BLK
origString = Replace$(origString, ChrW$(12289), ",")


origString = Replace$(origString, ChrW$(-97), "<special id=""74""/>") 'Degree
origString = Replace$(origString, ChrW$(8451), "<special id=""74""/>C") 'DegreeC
origString = Replace$(origString, ChrW$(-162), "<special id=""22""/>") 'Tilde
origString = Replace$(origString, ChrW$(-257), "")
origString = Replace$(origString, ChrW$(-1279), "")
origString = Replace$(origString, ChrW$(-3937), "<special id=""81""/>") 'Box

origString = Replace$(origString, ChrW$(168), "")
origString = Replace$(origString, ChrW$(212), "")
origString = Replace$(origString, ChrW$(173), "-")
origString = Replace$(origString, ChrW$(-248), "(")




For x = 1 To Len(origString)
xStr = Mid(origString, x, 1)
xCode = AscW(xStr)
If xCode > 127 Or xCode < 32 Then Stop
Next x

Cells(r, c).value = origString
End If


Next c
Next r


End Sub


Sub removeBox()


Dim shSource As Worksheet
Dim x, lrow As Long

Set shSource = ActiveSheet

lrow = shSource.Cells(Rows.count, 1).End(xlUp).row

For x = 1 To lrow
If AscW(Left(shSource.Cells(x, 1).value, 1)) = 1 Then
shSource.Cells(x, 1).NumberFormat = "@"
shSource.Cells(x, 1).value = Right(shSource.Cells(x, 1).value, Len(shSource.Cells(x, 1).value) - 1)
End If
Next x

End Sub


[TABLE="width: 500"]
<tbody>[TR]
[TD]P/N
[/TD]
[TD]Series[/TD]
[TD]Overview[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]AT Series[/TD]
[TD]<style id="1"> Honeywell AT Series – 2 pole, 3 position, Solder terminal, Standard Lever, Toggle Switch:</style>


MICRO SWITCH AT toggle switches provide a compact multi-pole toggle switch available as a 1-, 2-, 3-, 4-, 6-, 8-, or 10-pole design for a wide range of applications with a metal toggle lever, metal bushing, and metal housing for durability. Select AT Series are qualified to MIL-PRF-8805 for military applications. Options for the AT Series include panel seals and lever-to-bushing seals. These options enhance product performance where environments of dust or water spray could be present in the application. MICRO SWITCH AT toggle switch assemblies are available with individual poles (basic switches) which are UL, CSA, and CE certified.

<style id="1">Features:</style>
<special id="66"/> Gold or silver contacts
<special id="66"/> Choice of sealed bushing
<special id="66"/> Standard or pull to unlock toggle lever
<special id="66"/> Short behind-panel depth
<special id="66"/> Solder or quick connect termination
<special id="66"/> 2,4, 6, or 8 SPDT precision basic switches
<special id="66"/> 2 or 3 position, momentary or maintained action
<special id="66"/> 1 Standard toggle, tab, or pull-to-unlock levers.
<special id="66"/> Silver or gold contacts.
<special id="66"/> 15⁄32-inch bushing.
<special id="66"/> UL recognized, CSA certified basic switches.
<special id="66"/> Lever-to-bushing seal option


<style id="1">Applications:</style>
<special id="66"/> Industrial machinery and equipment
<special id="66"/> Military and commercial aviation
<special id="66"/> Construction equipment
<special id="66"/> Test instruments
<special id="66"/> Agricultural machinery
<special id="66"/> Process control
<special id="66"/> Medical instrumentation


[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]AT Series
[/TD]
[TD]<style id="1"> Honeywell AT Series <special id="9"/> 2 pole, 3 position, Solder terminal, Standard Lever, Toggle Switch:</style><special id="70"/><special id="70"/>MICRO SWITCH AT toggle switches provide a compact multi-pole toggle switch available as a 1-, 2-, 3-, 4-, 6-, 8-, or 10-pole design for a wide range of applications with a metal toggle lever, metal bushing, and metal housing for durability. Select AT Series are qualified to MIL-PRF-8805 for military applications. Options for the AT Series include panel seals and lever-to-bushing seals. These options enhance product performance where environments of dust or water spray could be present in the application. MICRO SWITCH AT toggle switch assemblies are available with individual poles (basic switches) which are UL, CSA, and CE certified.<special id="70"/> <special id="70"/><style id="1">Features:</style><special id="70"/><special id="66"/> Gold or silver contacts<special id="70"/><special id="66"/> Choice of sealed bushing<special id="70"/><special id="66"/> Standard or pull to unlock toggle lever<special id="70"/><special id="66"/> Short behind-panel depth<special id="70"/><special id="66"/> Solder or quick connect termination<special id="70"/><special id="66"/> 2,4, 6, or 8 SPDT precision basic switches<special id="70"/><special id="66"/> 2 or 3 position, momentary or maintained action<special id="70"/><special id="66"/> 1 Standard toggle, tab, or pull-to-unlock levers.<special id="70"/><special id="66"/> Silver or gold contacts.<special id="70"/><special id="66"/> 15⁄32-inch bushing.<special id="70"/><special id="66"/> UL recognized, CSA certified basic switches.<special id="70"/><special id="66"/> Lever-to-bushing seal option<special id="70"/><special id="70"/><style id="1">Applications:</style><special id="70"/><special id="66"/> Industrial machinery and equipment<special id="70"/><special id="66"/> Military and commercial aviation<special id="70"/><special id="66"/> Construction equipment<special id="70"/><special id="66"/> Test instruments<special id="70"/><special id="66"/> Agricultural machinery<special id="70"/><special id="66"/> Process control<special id="70"/><special id="66"/> Medical instrumentation<special id="70"/>[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]AT Series[/TD]
[TD]<style id="1"> Honeywell AT Series <special id="9"/> 2 pole, 3 position, Solder terminal, Standard Lever, Toggle Switch:</style><special id="70"/><special id="70"/>MICRO SWITCH AT toggle switches provide a compact multi-pole toggle switch available as a 1-, 2-, 3-, 4-, 6-, 8-, or 10-pole design for a wide range of applications with a metal toggle lever, metal bushing, and metal housing for durability. Select AT Series are qualified to MIL-PRF-8805 for military applications. Options for the AT Series include panel seals and lever-to-bushing seals. These options enhance product performance where environments of dust or water spray could be present in the application. MICRO SWITCH AT toggle switch assemblies are available with individual poles (basic switches) which are UL, CSA, and CE certified.<special id="70"/> <special id="70"/><style id="1">Features:</style><special id="70"/><special id="66"/> Gold or silver contacts<special id="70"/><special id="66"/> Choice of sealed bushing<special id="70"/><special id="66"/> Standard or pull to unlock toggle lever<special id="70"/><special id="66"/> Short behind-panel depth<special id="70"/><special id="66"/> Solder or quick connect termination<special id="70"/><special id="66"/> 2,4, 6, or 8 SPDT precision basic switches<special id="70"/><special id="66"/> 2 or 3 position, momentary or maintained action<special id="70"/><special id="66"/> 1 Standard toggle, tab, or pull-to-unlock levers.<special id="70"/><special id="66"/> Silver or gold contacts.<special id="70"/><special id="66"/> 15⁄32-inch bushing.<special id="70"/><special id="66"/> UL recognized, CSA certified basic switches.<special id="70"/><special id="66"/> Lever-to-bushing seal option<special id="70"/><special id="70"/><style id="1">Applications:</style><special id="70"/><special id="66"/> Industrial machinery and equipment<special id="70"/><special id="66"/> Military and commercial aviation<special id="70"/><special id="66"/> Construction equipment<special id="70"/><special id="66"/> Test instruments<special id="70"/><special id="66"/> Agricultural machinery<special id="70"/><special id="66"/> Process control<special id="70"/><special id="66"/> Medical instrumentation<special id="70"/>[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]AT Series[/TD]
[TD]<style id="1"> Honeywell AT Series – 2 pole, 3 position, Solder terminal, Standard Lever, Toggle Switch:</style>


MICRO SWITCH AT toggle switches provide a compact multi-pole toggle switch available as a 1-, 2-, 3-, 4-, 6-, 8-, or 10-pole design for a wide range of applications with a metal toggle lever, metal bushing, and metal housing for durability. Select AT Series are qualified to MIL-PRF-8805 for military applications. Options for the AT Series include panel seals and lever-to-bushing seals. These options enhance product performance where environments of dust or water spray could be present in the application. MICRO SWITCH AT toggle switch assemblies are available with individual poles (basic switches) which are UL, CSA, and CE certified.

<style id="1">Features:</style>
<special id="66"/> Gold or silver contacts
<special id="66"/> Choice of sealed bushing
<special id="66"/> Standard or pull to unlock toggle lever
<special id="66"/> Short behind-panel depth
<special id="66"/> Solder or quick connect termination
<special id="66"/> 2,4, 6, or 8 SPDT precision basic switches
<special id="66"/> 2 or 3 position, momentary or maintained action
<special id="66"/> 1 Standard toggle, tab, or pull-to-unlock levers.
<special id="66"/> Silver or gold contacts.
<special id="66"/> 15⁄32-inch bushing.
<special id="66"/> UL recognized, CSA certified basic switches.
<special id="66"/> Lever-to-bushing seal option


<style id="1">Applications:</style>
<special id="66"/> Industrial machinery and equipment
<special id="66"/> Military and commercial aviation
<special id="66"/> Construction equipment
<special id="66"/> Test instruments
<special id="66"/> Agricultural machinery
<special id="66"/> Process control
<special id="66"/> Medical instrumentation


[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]AT Series[/TD]
[/TR]
</tbody>[/TABLE]

 

[kbrummert]

Looks like your predecesor put a stop statement to flush out any "new" weird characters so he could add it to the list. I inserted a msgbox where the stop statement was and found it was 8260 - fraction slash. I then added a line in your code (in order where the line break is in the long list of replacement rows) to replace weird fraction slash with normal fraction slash (47), and it runs without stopping. Hopefully that is what you were looking for.

Sub cleanAttributes()


'********************************************************************************
'* This macro will replace special characters with the Agility special
'* character tag.
'* It will also trim off any extra white-space characters at the beginning or
'* end of the text.
'* As written, this macro is expecting the text to be modified to be in Column B and over
'********************************************************************************


Dim lrow, lcol As Long
Dim x, r, c As Long
Dim xCode As Long
Dim origString As String

lrow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Cells(1, Columns.Count).End(xlToLeft).Column

For x = 2 To lcol
Columns(x).NumberFormat = "@"
Next x

For r = 2 To lrow
For c = 2 To lcol
If Cells(r, c).Value <> "" Then
origString = Cells(r, c).Text

origString = Trim(origString)

' Anything less than 32, remove
For I = 1 To 9
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(13) & ChrW$(10), "") '
origString = Replace$(origString, ChrW$(13), "") '
origString = Replace$(origString, ChrW$(10), "") '
origString = Replace$(origString, ChrW$(11), "")
origString = Replace$(origString, ChrW$(12), "")
For I = 14 To 31
origString = Replace$(origString, ChrW$(I), "")
Next I

origString = Replace$(origString, ChrW$(37), "") '%
origString = Replace$(origString, ChrW$(38), "") '&
origString = Replace$(origString, ChrW$(64), "") '@
origString = Replace$(origString, ChrW$(126), "") '~


' 128 to 159, remove
For I = 128 To 159
origString = Replace$(origString, ChrW$(I), "")
Next I

origString = Replace$(origString, ChrW$(160), " ")
origString = Replace$(origString, ChrW$(161), "i")
origString = Replace$(origString, ChrW$(162), "") 'CENT
origString = Replace$(origString, ChrW$(163), "") 'Pound (money)
origString = Replace$(origString, ChrW$(164), "") 'Box
origString = Replace$(origString, ChrW$(165), "") 'Yen
origString = Replace$(origString, ChrW$(167), "") 'Section
origString = Replace$(origString, ChrW$(169), "") 'Copyright
origString = Replace$(origString, ChrW$(172), "")
origString = Replace$(origString, ChrW$(174), "") 'Registered Mark
origString = Replace$(origString, ChrW$(176), "") 'Degree
origString = Replace$(origString, ChrW$(177), "") 'Plus/Minus
origString = Replace$(origString, ChrW$(178), "") 'Square
origString = Replace$(origString, ChrW$(179), "") 'Cube
origString = Replace$(origString, ChrW$(180), "'")
origString = Replace$(origString, ChrW$(181), "") 'Micro
origString = Replace$(origString, ChrW$(182), "") 'Paragraph
origString = Replace$(origString, ChrW$(183), "") 'Bullet
origString = Replace$(origString, ChrW$(185), "")
origString = Replace$(origString, ChrW$(186), "") 'Ellipsis
origString = Replace$(origString, ChrW$(188), "") '1/4
origString = Replace$(origString, ChrW$(189), "") '1/2
origString = Replace$(origString, ChrW$(190), "") '3/4
origString = Replace$(origString, ChrW$(213), "'")
origString = Replace$(origString, ChrW$(214), "") 'O with umlaut
origString = Replace$(origString, ChrW$(215), "") 'Times Symbol
origString = Replace$(origString, ChrW$(216), "") 'Phi, Capital
origString = Replace$(origString, ChrW$(220), "") 'U with Umlaut
origString = Replace$(origString, ChrW$(233), "") 'E-accent
origString = Replace$(origString, ChrW$(247), "") 'Division Sign
origString = Replace$(origString, ChrW$(248), "") 'Phi, lower case
origString = Replace$(origString, ChrW$(934), "") 'Phi, lower case
origString = Replace$(origString, ChrW$(402), "") 'F latin
origString = Replace$(origString, ChrW$(650), "") 'Omega
origString = Replace$(origString, ChrW$(730), "") 'Degree
origString = Replace$(origString, ChrW$(778), "0") 'Degree
origString = Replace$(origString, ChrW$(913), "") 'Alpha
origString = Replace$(origString, ChrW$(915), "") 'Gamma
origString = Replace$(origString, ChrW$(916), "") 'Delta
origString = Replace$(origString, ChrW$(920), "") 'Theta
origString = Replace$(origString, ChrW$(923), "") 'Lambda
origString = Replace$(origString, ChrW$(931), "") 'Sigma
origString = Replace$(origString, ChrW$(937), "") 'Omega
origString = Replace$(origString, ChrW$(945), "") ' Alpha LC
origString = Replace$(origString, ChrW$(946), "") 'Beta
origString = Replace$(origString, ChrW$(947), "") 'Gamma
origString = Replace$(origString, ChrW$(949), "") 'Epsilon LC
origString = Replace$(origString, ChrW$(951), "") 'Eta LC
origString = Replace$(origString, ChrW$(952), "") 'Theta
origString = Replace$(origString, ChrW$(955), "") 'Lambda
origString = Replace$(origString, ChrW$(956), "") 'Micro
origString = Replace$(origString, ChrW$(960), "") 'Pi
origString = Replace$(origString, ChrW$(964), "") 'Tau LC
origString = Replace$(origString, ChrW$(8208), "") 'Minus
origString = Replace$(origString, ChrW$(8211), "") 'Minus
origString = Replace$(origString, ChrW$(8212), "") 'Long Dash
origString = Replace$(origString, ChrW$(8216), "") 'Quote Open Single
origString = Replace$(origString, ChrW$(8217), "") 'Quote Close Single
origString = Replace$(origString, ChrW$(8217), "") 'Apostrophe
origString = Replace$(origString, ChrW$(8220), "") 'Quotes Open Double
origString = Replace$(origString, ChrW$(8221), "") 'Inch Mark
origString = Replace$(origString, ChrW$(8221), "") 'Quotes Close Double
origString = Replace$(origString, ChrW$(8224), "") 'Dagger
origString = Replace$(origString, ChrW$(8225), "") 'Double Dagger
origString = Replace$(origString, ChrW$(8226), "") 'Bullet
origString = Replace$(origString, ChrW$(8230), "") 'Ellipsis
origString = Replace$(origString, ChrW$(8242), "") 'Foot Mark

origString = Replace$(origString, ChrW$(8260), "/") 'fraction slash

origString = Replace$(origString, ChrW$(8304), "") 'Degree
origString = Replace$(origString, ChrW$(8482), "") 'Trademark
origString = Replace$(origString, ChrW$(8486), "") 'Omega
origString = Replace$(origString, ChrW$(8594), "") 'Arrow Rt.
origString = Replace$(origString, ChrW$(8709), "") 'Phi Capital
origString = Replace$(origString, ChrW$(8710), "") 'Differenetial
origString = Replace$(origString, ChrW$(8725), "") 'Fraction Slash
origString = Replace$(origString, ChrW$(8730), "") 'Square-root
origString = Replace$(origString, ChrW$(8734), "") 'Infinity
origString = Replace$(origString, ChrW$(8776), "") 'Approx. Equal
origString = Replace$(origString, ChrW$(8800), "") 'Not Equal
origString = Replace$(origString, ChrW$(8804), "") 'Less than or equal
origString = Replace$(origString, ChrW$(8805), "") 'Greater than or equal
origString = Replace$(origString, ChrW$(9633), "") 'Box
origString = Replace$(origString, ChrW$(9642), "") 'Square BLK
origString = Replace$(origString, ChrW$(12289), ",")


origString = Replace$(origString, ChrW$(-97), "") 'Degree
origString = Replace$(origString, ChrW$(8451), "C") 'DegreeC
origString = Replace$(origString, ChrW$(-162), "") 'Tilde
origString = Replace$(origString, ChrW$(-257), "")
origString = Replace$(origString, ChrW$(-1279), "")
origString = Replace$(origString, ChrW$(-3937), "") 'Box

origString = Replace$(origString, ChrW$(168), "")
origString = Replace$(origString, ChrW$(212), "")
origString = Replace$(origString, ChrW$(173), "-")
origString = Replace$(origString, ChrW$(-248), "(")




For x = 1 To Len(origString)
xStr = Mid(origString, x, 1)
xCode = AscW(xStr)

If xCode > 127 Or xCode < 32 Then MsgBox (xCode) 'Stop
Next x

Cells(r, c).Value = origString
End If


Next c
Next r


End Sub


Sub removeBox()


Dim shSource As Worksheet
Dim x, lrow As Long

Set shSource = ActiveSheet

lrow = shSource.Cells(Rows.Count, 1).End(xlUp).Row

For x = 1 To lrow
If AscW(Left(shSource.Cells(x, 1).Value, 1)) = 1 Then
shSource.Cells(x, 1).NumberFormat = "@"
shSource.Cells(x, 1).Value = Right(shSource.Cells(x, 1).Value, Len(shSource.Cells(x, 1).Value) - 1)
End If
Next x

End Sub

 

[jenowell]

That fixed the error you found. But, I get these all the time with different errors. When I ran your version of the Macro it gave me a popup box with a number 9569 and another box with 177. What does the number refer to? Is there a way I can see what cell has the error? and/or what it doesn't like?

 

[Fluff]

Untested, but change the msgbox line to this

            If xCode > 127 Or xCode < 32 Then MsgBox (xCode & "Character No " & x & vbLf & "Row " & r & vbLf & "Col " & c) 'Stop

This will give the number you already get along with its position in the string, then the row it occurs on followed by the col number

 

[jenowell]

That works!!!!!!!! This will save me so much time in tracking down what is wrong. Thank you so much.

 

[Fluff]

Glad we could help & thanks for the feedback