Problem retrieving text data from cell

[Ragnar Midtskogen]

Hello,

I am parsing a spreadsheet and as part of the process I have to check what
is in the cells in column A.
The content is either a numeric character string, "019" for example, or
"#N/A"
The code below works fine as long as the cell contains a numeric string, but
when it contains "#N/A"
the behavior is strange.

Dim oSourceSheet As WorkSheet

If I use the Text propery, like this:
sRegionNumber = oSourceSheet.Cells(lnFirstRow, 1).Text
sRegionNumber is set to a Null string.

If I use the default propery (Value?), like this
sRegionNumber = oSourceSheet.Cells(lnFirstRow, 1)
sRegionNumber is set to "Error 2042"

I suppose I can use the text property and check for null string, but then I
would not be able to detect
the case if the cell did contain a null string.

Any help with this would be very much appreciated.

Ragnar

 

[Nick Hodge]

Ragnar

Difficult to tell from the smal amount of code, but looking at your prefix
are you declaring the oSourceSheet as the generic Object type or
specifically as a Worksheet. This may give you trouble.

Additionally I would say that if you are controlling the XL object, you
could use the worksheet functions ISNA() or ISTEXT() to discover the value
in the cell

xlApp.WorksheetFunction.IsNa(oSourceSheet.Cells(lnFirstRow, 1).Text)

Will return a boolean result (True/False)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Ragnar Midtskogen]

Thank you Nick,

I was not familiar with the WorkSheetFunction, but it looks like I should be
able to use a couple of the IS functions to achieve my ends.

Since this sheet does not contain any formulas I thought I could just
retrieve what was in each cell and check what it was. I guess I have a few
things to learn about Excel.

I have no idea how the sheet was generated, but it contains sales data and I
think a spreadsheet is used simply because it makes it easy to display the
data in tabular form.

I am supposed to write a program to parse it into separate files by sales
district, and the district number is in the first column. However, there are
some rows without data, and in those rows column A contains #N/A. Two or
more rows of #N/A signals the end of each sheet.

BTW, I am declaring the worksheet as a WorkSheet, as I showed in my post.

Ragnar

 

[Nick Hodge]

Ragnar

Sorry. didn't spot the declaration

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)