Stuart,

Errors will show up when importing text files if Excel sees an
invalid formula. Hyphens or equal signs in your text are a cause.
Something like "=smith" or "-actual" throws an error.
Try revising your import criteria to cure this.

If you still get errors, then the following sample code could work.

'------------------------------------------------------
Sub TestCode()
Dim objRange As Range
Dim objArea As Range
Dim objCell As Range

Set objRange = Application.Intersect(Range("E:E"), ActiveSheet.UsedRange)
Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors)

For Each objArea In objRange.Areas
For Each objCell In objArea
objCell.Offset(0, 14).Value = "Err in " & objCell.Address
objCell.Clear
Next 'ojbCell
Next 'objArea

Set objCell = Nothing
Set objArea = Nothing
Set objRange = Nothing
End Sub
'------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA

"Stuart" wrote in message
...
I am trying to trap #NAME? occurences in column E.
These have occurred because of the import of a text
file. Excel seems to consider the cell contents to be some
sort of formula, is my guess.
Firstly, is there any way to get at the actual data? All I
can see in the cell is #NAME?.
If not, then I would like to note the address of such cells
out in col S, and then clear the contents of #NAME cells.
I have this:

Sub TestSplitQtyUnit()
Dim C As Range, x As Variant, i As Integer, LoopCounter As Integer
With ActiveSheet
For Each C In Intersect(Range("E:E"), ActiveSheet.UsedRange)
On Error GoTo ErrHandler
If Not IsEmpty(C) Then
x = Split(C, " ")
LoopCounter = 1
For i = 0 To UBound(x)
C.Offset(0, LoopCounter + 3).Value = x(i)
LoopCounter = LoopCounter + 1
Next
End If
GoTo Line1
ErrHandler:
C.Offset(0, 14).Value = C.Address
C.ClearContents
Line1:
Next
End With
End Sub

which works for the first time it meets #NAME? but the 2nd
time it errors on the line
x=Split(C, " ")
with a Type mismatch error.
Why will this not deal with subsequent occurrences of
#NAME? please?
Regards.