#Name Error in source data 1

[cmayer10]

All,
I have an excel spreadsheet that I am trying to work with that is output from our organization's financial system. When I open the source file in excel, excel tries to read some of the text as formulas. The text in one cell is "-Room and Board"; I'm assuming excel sees the dash as a neagtive sign and tries to read it as a formula. Excel changes the text to "=-Room and Board", and then shows a #Name error, since that is not a valid formula. Then whenever I try to use the following VBA to work with the particular cell I get an type-mismatch error.

mystring = myworksheet.Cells(1,1).Value

Is there a way to programatically keep Excel from trying to read the cell as a formula?

Thanks,
Collen

 

[SkipVought]

Hi,

Change the cell Number Format to TEXT.

 

[cmayer10]

Thanks, SkipVought. I tried that already and the #Name error remains and I still get the VBA error also.

 

[SkipVought]

EDIT the cell and ENTER

 

[cmayer10]

Right, so that does work, if I do it manually one by one. But since this will be a report we are running regularly, I need to fix the problem programmatically. And even if I do something like

mystring = myworksheet.Columns(1).NumberFormat = "@"

I still get the datamismatch error, and the spreadsheet still shows #Name.

 

[SkipVought]

Here's the issue:

When the first character in a cell is -, Excel ASSUMES that what will follow will be either a NUMBER, a FUNCTION or a NAMED RANGE. When what follows is none of the above, the #NAME? Error results.

To prevent such from occurring, change the Numbrr Format in the column BEFORE entering any such data.

 

[cmayer10]

But alas,the data is output from our financial software so I have to deal with it after the fact. Is there nothing I can do programmatically to fix it?

 

[SkipVought]

Select the column

DATA > Text to Columns > Delimited > (check TAB box) > select TEXT and FINISH

 

[cmayer10]

Perfect! Thanks so much.

 

[SkipVought]

If the = is in there, then use = as the delimiter, which will eliminate the = from the cell.

Turn on your Macro Recorder and record performing the entire process. Store the macro in the PRESONAL.XLSB workbook, so the macro will be available to run any time you have Excel open.