Correcting #NAME errors

[Tim Childs]

Hi

I have a small piece of code that appears to work fine on
small files but when the code is applied to a large file
with a very large number of #NAME errors it "hangs".The VB
code snippet is produced below. (The #NAME errors arise in
a CSV file where the first character in a text field is
a "-").

Please can someone shed some light on this peculiarity?

THANKS

Tim



Dim rTemp As Range

Set rTemp = Cells.Find(What:="#Name?", After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False)
Do While Not rTemp Is Nothing
'Application.ScreenUpdating = False
If Not rTemp Is Nothing Then
With rTemp
.Value = Chr(39) & Mid(.Formula, 2, Len
(.Formula) - 1)
End With
End If
Set rTemp = Cells.Find(What:="#Name?",
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False)
Loop

 

[Dave Peterson]

I think that excel sees that dash as a minus and figures you're writing a
formula.

You could rename your .csv file to .txt and then import and really specify Text
for that field (and you won't have the trouble).

Or you could look for the errors like you're doing.

Or this worked for me in light testing.
Select all the cells
edit|goto|special
formulas & errors only.
Edit|replace
=-
with
'-

As a macro:

Option Explicit
Sub testme01()

Dim myErrRng As Range

Set myErrRng = Nothing
On Error Resume Next
Set myErrRng = ActiveSheet.UsedRange _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If myErrRng Is Nothing Then
'do nothing
Else
myErrRng.Replace What:="=-", Replacement:="'-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
End Sub

======
I think if I were doing it, I'd do the .txt stuff. I think having more control
is better.

===
But I ran your code (with a minor change near the bottom:
Set rTemp = Cells.FindNext(rTemp)
instead of the .find you used again)

And it worked ok with about 27000 cells that needed fixing--it didn't hang up at
all.

 

[Tim Childs]

Dave

Thanks for your help on this. It is much appreciated.

The proc you sent worked real fast but I need to double
check that it would not have any "false positives" or
uninended consequences.

That final tip about using Find Next seems to have done
the trick on some initial testing on a large file.

Unfortunately, I do not have the "luxury" of going down
the TXT route as the VB is part of a larger formatting
routine used by people who are not comfortable renaming
files (and it would be slow).

Thanks again

Tim