VB code to remove 30 special characters

[sshrikanth2]

Dear All,

I need your help...

I have a coding to remove 30 special characters from "column A" but when i run this code a error message shows " Compile error: Expected: line number or label or statement or end of statement" and below i have highlighted error line code in Red.

Thanking you in anticipation.

Following is the coding :


Sub Remove_Characters()
Dim Count As Integer
Dim Target As String
Dim Cell As Object
Dim N As Integer
Dim Count2 As Integer
Dim Target2 As String
Dim Cell2 As Object
Dim N2 As Integer
Dim Count3 As Integer
Dim Target3 As String
Dim Cell3 As Object
Dim N3 As Integer
Dim Count4 As Integer
Dim Target4 As String
Dim Cell4 As Object
Dim N4 As Integer
Dim Count5 As Integer
Dim Target5 As String
Dim Cell5 As Object
Dim N5 As Integer
Dim Count6 As Integer
Dim Target6 As String
Dim Cell6 As Object
Dim N6 As Integer
Dim Count7 As Integer
Dim Target7 As String
Dim Cell7 As Object
Dim N7 As Integer
Dim Count8 As Integer
Dim Target8 As String
Dim Cell8 As Object
Dim N8 As Integer
Dim Count9 As Integer
Dim Target9 As String
Dim Cell9 As Object
Dim N9 As Integer
Dim Count10 As Integer
Dim Target10 As String
Dim Cell10 As Object
Dim N10 As Integer
Dim Count11 As Integer
Dim Target11 As String
Dim Cell11 As Object
Dim N11 As Integer
Dim Count12 As Integer
Dim Target12 As String
Dim Cell12 As Object
Dim N12 As Integer
Dim Count13 As Integer
Dim Target13 As String
Dim Cell13 As Object
Dim N13 As Integer
Dim Count14 As Integer
Dim Target14 As String
Dim Cell14 As Object
Dim N14 As Integer
Dim Count15 As Integer
Dim Target15 As String
Dim Cell15 As Object
Dim N15 As Integer
Dim Count16 As Integer
Dim Target16 As String
Dim Cell16 As Object
Dim N16 As Integer
Dim Count17 As Integer
Dim Target17 As String
Dim Cell17 As Object
Dim N17 As Integer
Dim Count18 As Integer
Dim Target18 As String
Dim Cell18 As Object
Dim N18 As Integer
Dim Count19 As Integer
Dim Target19 As String
Dim Cell19 As Object
Dim N19 As Integer
Dim Count20 As Integer
Dim Target20 As String
Dim Cell20 As Object
Dim N20 As Integer
Dim Count21 As Integer
Dim Target21 As String
Dim Cell21 As Object
Dim N21 As Integer
Dim Count22 As Integer
Dim Target22 As String
Dim Cell22 As Object
Dim N22 As Integer
Dim Count23 As Integer
Dim Target23 As String
Dim Cell23 As Object
Dim N23 As Integer
Dim Count24 As Integer
Dim Target24 As String
Dim Cell24 As Object
Dim N24 As Integer
Dim Count25 As Integer
Dim Target25 As String
Dim Cell25 As Object
Dim N25 As Integer
Dim Count26 As Integer
Dim Target26 As String
Dim Cell26 As Object
Dim N26 As Integer
Dim Count27 As Integer
Dim Target27 As String
Dim Cell27 As Object
Dim N27 As Integer
Dim Count28 As Integer
Dim Target28 As String
Dim Cell28 As Object
Dim N28 As Integer
Dim Count29 As Integer
Dim Target29 As String
Dim Cell29 As Object
Dim N29 As Integer
Dim Count30 As Integer
Dim Target30 As String
Dim Cell30 As Object
Dim N30 As Integer
Count = 0
Target = "`"
For Each Cell In ActiveSheet.Range("A:A")
N = InStr(1, Cell.Value, Target)
While N <> 0
Count = Count + 1
N = InStr(N + 1, Cell.Value, Target)
Wend
Next Cell
Count2 = 0
Target2 = "!"
For Each Cell In ActiveSheet.Range("A:A")
N2 = InStr(1, Cell.Value, Target2)
While N2 <> 0
Count2 = Count2 + 1
N2 = InStr(N2 + 1, Cell.Value, Target2)
Wend
Next Cell
Count3 = 0
Target3 = "@"
For Each Cell In ActiveSheet.Range("A:A")
N3 = InStr(1, Cell.Value, Target3)
While N3 <> 0
Count3 = Count3 + 1
N3 = InStr(N3 + 1, Cell.Value, Target3)
Wend
Next Cell
Count4 = 0
Target4 = "#"
For Each Cell In ActiveSheet.Range("A:A")
N4 = InStr(1, Cell.Value, Target4)
While N4 <> 0
Count4 = Count4 + 1
N4 = InStr(N4 + 1, Cell.Value, Target4)
Wend
Next Cell
Count5 = 0
Target5 = "$"
For Each Cell In ActiveSheet.Range("A:A")
N5 = InStr(1, Cell.Value, Target5)
While N5 <> 0
Count5 = Count5 + 1
N5 = InStr(N5 + 1, Cell.Value, Target5)
Wend
Next Cell
Count6 = 0
Target6 = "%"
For Each Cell In ActiveSheet.Range("A:A")
N6 = InStr(1, Cell.Value, Target6)
While N6 <> 0
Count6 = Count6 + 1
N6 = InStr(N6 + 1, Cell.Value, Target6)
Wend
Next Cell
Count7 = 0
Target7 = "^"
For Each Cell In ActiveSheet.Range("A:A")
N7 = InStr(1, Cell.Value, Target7)
While N7 <> 0
Count7 = Count7 + 1
N7 = InStr(N7 + 1, Cell.Value, Target7)
Wend
Next Cell
Count8 = 0
Target8 = "&"
For Each Cell In ActiveSheet.Range("A:A")
N8 = InStr(1, Cell.Value, Target8)
While N8 <> 0
Count8 = Count8 + 1
N8 = InStr(N8 + 1, Cell.Value, Target8)
Wend
Next Cell
Count9 = 0
Target9 = "("
For Each Cell In ActiveSheet.Range("A:A")
N9 = InStr(1, Cell.Value, Target9)
While N9 <> 0
Count9 = Count9 + 1
N9 = InStr(N9 + 1, Cell.Value, Target9)
Wend
Next Cell
Count10 = 0
Target10 = ")"
For Each Cell In ActiveSheet.Range("A:A")
N10 = InStr(1, Cell.Value, Target10)
While N10 <> 0
Count10 = Count10 + 1
N10 = InStr(N10 + 1, Cell.Value, Target10)
Wend
Next Cell
Count11 = 0
Target11 = "_"
For Each Cell In ActiveSheet.Range("A:A")
N11 = InStr(1, Cell.Value, Target11)
While N11 <> 0
Count11 = Count11 + 1
N11 = InStr(N11 + 1, Cell.Value, Target11)
Wend
Next Cell
Count12 = 0
Target12 = "-"
For Each Cell In ActiveSheet.Range("A:A")
N12 = InStr(1, Cell.Value, Target12)
While N12 <> 0
Count12 = Count12 + 1
N12 = InStr(N12 + 1, Cell.Value, Target12)
Wend
Next Cell
Count13 = 0
Target13 = "+"
For Each Cell In ActiveSheet.Range("A:A")
N13 = InStr(1, Cell.Value, Target13)
While N13 <> 0
Count13 = Count13 + 1
N13 = InStr(N13 + 1, Cell.Value, Target13)
Wend
Next Cell
Count14 = 0
Target14 = "="
For Each Cell In ActiveSheet.Range("A:A")
N14 = InStr(1, Cell.Value, Target14)
While N14 <> 0
Count14 = Count14 + 1
N14 = InStr(N14 + 1, Cell.Value, Target14)
Wend
Next Cell
Count15 = 0
Target15 = "{"
For Each Cell In ActiveSheet.Range("A:A")
N15 = InStr(1, Cell.Value, Target15)
While N15 <> 0
Count15 = Count15 + 1
N15 = InStr(N15 + 1, Cell.Value, Target15)
Wend
Next Cell
Count16 = 0
Target16 = "}"
For Each Cell In ActiveSheet.Range("A:A")
N16 = InStr(1, Cell.Value, Target16)
While N16 <> 0
Count16 = Count16 + 1
N16 = InStr(N16 + 1, Cell.Value, Target16)
Wend
Next Cell
Count17 = 0
Target17 = "["
For Each Cell In ActiveSheet.Range("A:A")
N17 = InStr(1, Cell.Value, Target17)
While N17 <> 0
Count17 = Count17 + 1
N17 = InStr(N17 + 1, Cell.Value, Target17)
Wend
Next Cell
Count18 = 0
Target18 = "]"
For Each Cell In ActiveSheet.Range("A:A")
N18 = InStr(1, Cell.Value, Target18)
While N18 <> 0
Count18 = Count18 + 1
N18 = InStr(N18 + 1, Cell.Value, Target18)
Wend
Next Cell
Count19 = 0
Target19 = "|"
For Each Cell In ActiveSheet.Range("A:A")
N19 = InStr(1, Cell.Value, Target19)
While N19 <> 0
Count19 = Count19 + 1
N19 = InStr(N19 + 1, Cell.Value, Target19)
Wend
Next Cell
Count20 = 0
Target20 = "\"
For Each Cell In ActiveSheet.Range("A:A")
N20 = InStr(1, Cell.Value, Target20)
While N20 <> 0
Count20 = Count20 + 1
N20 = InStr(N20 + 1, Cell.Value, Target20)
Wend
Next Cell
Count21 = 0
Target21 = ":"
For Each Cell In ActiveSheet.Range("A:A")
N21 = InStr(1, Cell.Value, Target21)
While N21 <> 0
Count21 = Count21 + 1
N21 = InStr(N21 + 1, Cell.Value, Target21)
Wend
Next Cell
Count22 = 0
Target22 = ";"
For Each Cell In ActiveSheet.Range("A:A")
N22 = InStr(1, Cell.Value, Target22)
While N22 <> 0
Count22 = Count22 + 1
N22 = InStr(N22 + 1, Cell.Value, Target22)
Wend
Next Cell
Count23 = 0
Target23 = """"
For Each Cell In ActiveSheet.Range("A:A")
N23 = InStr(1, Cell.Value, Target23)
While N23 <> 0
Count23 = Count23 + 1
N23 = InStr(N23 + 1, Cell.Value, Target23)
Wend
Next Cell
Count24 = 0
Target24 = "'"
For Each Cell In ActiveSheet.Range("A:A")
N24 = InStr(1, Cell.Value, Target24)
While N24 <> 0
Count24 = Count24 + 1
N24 = InStr(N24 + 1, Cell.Value, Target24)
Wend
Next Cell
Count25 = 0
Target25 = "<"
For Each Cell In ActiveSheet.Range("A:A")
N25 = InStr(1, Cell.Value, Target25)
While N25 <> 0
Count25 = Count25 + 1
N25 = InStr(N25 + 1, Cell.Value, Target25)
Wend
Next Cell
Count26 = 0
Target26 = ">"
For Each Cell In ActiveSheet.Range("A:A")
N26 = InStr(1, Cell.Value, Target26)
While N26 <> 0
Count26 = Count26 + 1
N26 = InStr(N26 + 1, Cell.Value, Target26)
Wend
Next Cell
Count27 = 0
Target27 = ","
For Each Cell In ActiveSheet.Range("A:A")
N27 = InStr(1, Cell.Value, Target27)
While N27 <> 0
Count27 = Count27 + 1
N27 = InStr(N27 + 1, Cell.Value, Target27)
Wend
Next Cell
Count28 = 0
Target28 = "."
For Each Cell In ActiveSheet.Range("A:A")
N28 = InStr(1, Cell.Value, Target28)
While N28 <> 0
Count28 = Count28 + 1
N28 = InStr(N28 + 1, Cell.Value, Target28)
Wend
Next Cell
Count29 = 0
Target29 = "/"
For Each Cell In ActiveSheet.Range("A:A")
N29 = InStr(1, Cell.Value, Target29)
While N29 <> 0
Count29 = Count29 + 1
N29 = InStr(N29 + 1, Cell.Value, Target29)
Wend
Next Cell
Count30 = 0
Target30 = "?"
For Each Cell In ActiveSheet.Range("A:A")
N30 = InStr(1, Cell.Value, Target30)
While N30 <> 0
Count30 = Count30 + 1
N30 = InStr(N30 + 1, Cell.Value, Target30)
Wend
Next Cell
CountTotal = (Count + Count2 + Count3 + Count4 + Count5 + Count6 + Count7 + Count8 + Count9 + Count10 + Count11 + Count12 + Count13 + Count14 + Count15 + Count16 + Count17 + Count18 + Count19 + Count20 + Count21 + Count22 + Count23 + Count24 + Count25 + Count26 + Count27 + Count28 + Count29 + Count30)
If CountTotal = 0 Then
GoTo None
Else
End If
If MsgBox("This sheet contains " & CountTotal & " special characters - Do you wish to remove them?", vbYesNo) = vbYes Then
On Error GoTo Error_Check
Cells.Replace What:="`", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="!", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="@", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="#", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="%", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="^", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="&", Replacement:="AND", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="_", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="+", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="=", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="{", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="}", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="[", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="]", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="\", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="|", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=";", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="<", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=">", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="?", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
TotalD = Target & " = " & Count & vbCr & Target2 & " = " & Count2 & vbCr & Target3 & " = " & Count3 & vbCr & Target4 & " = " & Count4 & vbCr & Target5 & " = " & Count5 & vbCr & Target6 & " = " & Count6 & vbCr & Target7 & " = " & Count7 & vbCr & Target8 & " = " & Count8 & vbCr & Target9 & " = " & Count9 & vbCr & Target10 & " = " & Count10 & vbCr & Target11 & " = " & Count11 & vbCr & Target12 & " = " & Count12 & vbCr & Target13 & " = " & Count13 & vbCr & Target14 & " = " & Count14 & vbCr & Target15 & " = " & Count15 & vbCr & Target16 & " = " & Count16 & vbCr & Target17 & " = " & Count17 & vbCr & Target18 & " = " & Count18 & vbCr & Target19 & " = " & Count19 & vbCr & Target20 & " = " & Count20 & vbCr & Target21 & " = " & Count21 & vbCr & Target22 & " = " & Count22 & vbCr & Target23 & " = " & Count23 & vbCr & Target24 & " = " & Count24 & vbCr & Target25 & " = " & Count25 & vbCr & Target26 & " = " & Count26 & vbCr & Target27 & " = " & Count27 & vbCr & Target28 & " = " & Count28 & vbCr & Target29 & " = " & Count29 & vbCr & Target30 & " ? " & Count30
MsgBox " The following Special Characters have been removed!" & vbCr & vbCr & vbCr & TotalD
End If
None:
If CountTotal = 0 Then
MsgBox "Sorry! No Special Characters."
End If
Error_Check:
If Err.Number = "1004" Or Err.Number = "13" Then
MsgBox "Error Removing Characters!", vbCritical
End If
End Sub


Regards,

Srikanth M

 

[JLGWhiz]

I am going to take a wild guess and say that you have exceeded the 255 character limit for a string variable. (TotalID)

 

[shg]

Is it possible that you could explain in words, without reference to the code, exactly what you're trying to do?