Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Clean Me" Macro is giving "#VALUE!" error in the Notes field.
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Clean Me" Macro is giving "#VALUE!" error in the Notes field.
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 "Ryan Watkins" <Ryan wrote in 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Run macro only if Field E10 = "WO ID" and AB10 = "Spec Sizing" | Excel Discussion (Misc queries) | |||
Run macro only if Field E10 = "WO ID" and AB10 = "Spec Sizing" | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Utility to "clean up" or "defrag" large Excel file | Excel Discussion (Misc queries) |