Maybe:

Option Explicit
'
'' (single quote)
'" (double quote)
', (comma)
'* (asterisk)
'`(apostrophe)
'|(pipe)
'^(carrot)
'? (question mark)
'<(less than)
'(greater than)
'\ (escape or backslash)
'$ (dollar sign)

Sub testme02()

Application.ScreenUpdating = False

Dim myChars As Variant
Dim iCtr As Long
Dim wks As Worksheet
Dim newWks As Worksheet
Dim oRow As Long
Dim oCol As Long
Dim FoundCell As Range
Dim FirstAddress As String

myChars = Array("'", """", ",", "~*", "`", "|", "^", _
"~?", "<", "", "\", "$")

'notice the ~*, ~?. You'll need it for ~~, too.

Set newWks = Worksheets.Add
newWks.Range("a1").Resize(1, 4).Value = _
Array("SheetName", "Address", "Header", "Value")

oRow = 2
oCol = 1
For Each wks In ActiveWorkbook.Worksheets
With wks
If .Name = newWks.Name Then
'do nothing
Else
With .UsedRange
For iCtr = LBound(myChars) To UBound(myChars)
Application.StatusBar = "Processing: " _
& .Parent.Name & " char: " _
& Right(myChars(iCtr), 1)
Set FoundCell = .Find(What:=myChars(iCtr), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'not found on the sheet
Else
FirstAddress = FoundCell.Address
Do
newWks.Cells(oRow, oCol).Value _
= "'" & .Parent.Name
newWks.Cells(oRow, oCol + 1).Value _
= FoundCell.Address(0, 0)
newWks.Cells(oRow, oCol + 2).Value _
= "'" & .Parent.Cells(1, _
FoundCell.Column).Text
newWks.Cells(oRow, oCol + 3).Value _
= "'" & FoundCell.Text
oRow = oRow + 1
If oRow .Parent.Rows.Count Then
oCol = oCol + 4
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address < FirstAddress
End If
Next iCtr
End With
End If
End With
Next wks

newWks.UsedRange.Columns.AutoFit

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub


Michael wrote:

I am trying to clean up some data and I need to search about 20 tables (which
I have copied down to spreadsheets) and Identify the lines that contain one
or more of these special characters.

I have not been able to put all the components together in working fashion.

I plan on just doing them one at a time. I have been using a SUB() for just
the active sheet and running it on each sheet individually. This would be
just fine if it worked.

thanks for any help.

--

Dave Peterson