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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.596 / Virus Database: 379 - Release Date: 26/02/2004