Many thanks

However in this case no cells are found when the line
Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors)
runs.

I've got nearly 20,000 rows of data here, so big problems at the
moment !!

The data has come from a pdf file via the Text Import wizard. It
consists of blocks of data (generally in table format) but which are
interspersed with blocks of text spread across most/all of the columns.

The way this text is laid out is one main problem. The 2nd problem is
that the 'tables' do not always line up. This means that I can end up
with cell contents like: timber l and adjacent cell: umber.
So I'm having to test cells and split, so as to move l into umber to
give lumber (and timber).

The formula problem with = I had spotted, but the minus sign I had
not considered. Sure enough these particular #NAME cells are right
in the middle of a block of cells which only contain a minus sign
(presumably indicating no value in the original document).

If you've any ideas?

Regards and thanks.

"Jim Cone" wrote in message
...
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.




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