Stuart,

Here's an alternative way.

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 no_err
If Not IsEmpty(C.Value) Then
If C.Value = CVErr(xlErrName) Then
C.Offset(0, 14).Value = C.Address
C.ClearContents
End If
End If
no_err:
Next

End With

End Sub


By the way, why store the address, you must know what it is because you only
test column E, and you put it in the same row. I would colour the offending
cell myself.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"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.


---
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