"Clean Me" Macro is giving "#VALUE!" error in the Notes field.

[Guest]

I have exported my .PST file from Outlook as a .csv file and then I need to
clean up all the extra characters. I have a code for "Clean Me."
It is:
Sub CleanMe()
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange
cel = Application.Clean(cel) (Use the tab key to indent this line of the
code.)
Next cel
Application.ScreenUpdating = True
End Sub
But after i run it, it gives a "#VALUE!" error in some of the fields. All of
the fields that get messed up are the fields from the "Notes section" in
outlook. All of the notes fields don't mess up, but a lot do.

Please Help!

 

[Jim Cone]

Ryan,

The clean function is probably leaving a cell that Excel
thinks is an invalid formula.
The following mod takes care of most of that.

'--------------------------------
Sub CleanMe()
Dim cel As Excel.Range
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange
On Error Resume Next
cel.Value = Application.Clean(cel.Value)
If Err.Number <> 0 Then
'use a single apostrophe
cel.Value = "'" & Application.Clean(cel.Value)
Err.Clear
End If
Next cel
Application.ScreenUpdating = True
End Sub
'-------------------------------

Regards,
Jim Cone
San Francisco, USA


message I have exported my .PST file from Outlook as a .csv file and then I need to
clean up all the extra characters. I have a code for "Clean Me."
It is:
Sub CleanMe()
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange
cel = Application.Clean(cel) (Use the tab key to indent this line of the
code.)
Next cel
Application.ScreenUpdating = True
End Sub
But after i run it, it gives a "#VALUE!" error in some of the fields. All of
the fields that get messed up are the fields from the "Notes section" in
outlook. All of the notes fields don't mess up, but a lot do.

Please Help!