The code amendment works......many thanks.

Will also try your and Dave's suggestions.

Thanks to you all.

Regards.

"Jim Cone" wrote in message
...
Stuart,

Try Dave Peterson's suggestion of importing as text only.
That will probably work.

Something else to try is to:
import the data into one column
"Trim" the data
then use Data | Text to Columns

Otherwise try replacing...

Set objRange = objRange.SpecialCells(xlCellTypeFormulas, xlErrors)
with
Set objRange = objRange.SpecialCells(xlCellTypeConstants, xlErrors)

Regards,
Jim Cone
San Francisco, CA


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

- snip -




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