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

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