Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting #NAME errors
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting #NAME errors
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correcting #NAME errors
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Correcting Multiple Div#0s | Excel Worksheet Functions | |||
Help with correcting writing a formula | Excel Worksheet Functions | |||
Correcting an extrapolation macro | Excel Discussion (Misc queries) | |||
Correcting a #REF! | Excel Worksheet Functions | |||
need help in correcting the formula | Excel Worksheet Functions |