Trapping #NAME? question

[Stuart]

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.

 

[Bob Phillips]

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]

Thanks for that, but it seems to be giving pretty much the same
problem I had.
It removes #NAME? and pastes the address, and it will run past
the first acceptable data ok, but trips on the second cell which
contains acceptable data (ie the type of data I want to split)

On that 2nd occasion I get type mismatch on the line:
If C.Value = CVErr(xlErrName) Then

Regards.

 

[Jim Cone]

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

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:

 

[Dave Peterson]

Can you try to catch the error when you do the import?

Maybe making that field text instead of General????? would eliminate the
problem.

 

[Stuart]

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]

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

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.

- snip -

 

[Stuart]

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

Will also try your and Dave's suggestions.

Thanks to you all.

Regards.