Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixing Error 2029 (#NAME?)
Here is my problem, I am importing a CSV file that somewhere along the line got messed up. An alpha field managed to get a leading hyphen (ex: "-John Q Public"). When it gets imported, excel thinks it is a numeric value add an equals sign in front. Now the cell is an error (#NAME?). How can I programmatically fix the cell by removing the "=-"? I can identify it using IsError(rngCel.value), but I can not seem to modify the contents. Any suggestions?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixing Error 2029 (#NAME?)
"pb" wrote:
Here is my problem, I am importing a CSV file that somewhere along the line got messed up. An alpha field managed to get a leading hyphen (ex: "-John Q Public"). When it gets imported, excel thinks it is a numeric value add an equals sign in front. Now the cell is an error (#NAME?). How can I programmatically fix the cell by removing the "=-"? I can identify it using IsError(rngCel.value), but I can not seem to modify the contents. It sounds like you are __opening__ the CSV file, not __importing__ it per se. One suggestion: use the External Data Import Text wizard to truly __import__ the file. Then in the final menu, you might be able to select type Text for the entire column that contains names. That would avoid the problem altogether. I cannot give you step-by-step instructions because you neglected to say what version of Excel you are using. If the Import Text wizard does not work for you (for example, you cannot make the entire column Text because it contains a mix of data), then you can use the following paradigm: If IsError(rngCel.Value) Then rngCel.Value = "'" & Mid(rngCel.Formula, 2) End If In case the string constant is difficult to read in your font, that is double-quote single-quote (aka apostrophe) double-quote. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixing Error 2029 (#NAME?)
joeu2004,
Thanks for the help. I forgot to check rngCol.Formula. All I needed was: rngCol.Value = Mid(rngCol.Formula, 3) You are right, I am opening the CSV then fix the data before saving it. I have to do it this way because there are fields with imbeded CR, LF and Tab characters in them that really mess it up when I import it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 2029. How do I fix this error | Excel Programming | |||
Problem sorting by date after 2029 on Excel 2003 | Excel Discussion (Misc queries) | |||
Fixing a vlookup macro that returns #N/A error | Excel Discussion (Misc queries) | |||
Error 2029 using Find XL 97 | Excel Programming |