Safe way to check what is in a cell

  • Thread starter Thread starter Ragnar Midtskogen
  • Start date Start date
R

Ragnar Midtskogen

Hello,

I am writing an application that is supposed to parse a spreadsheet file
into individual files dependent on what is in the first column.
Each block of rows with the same numeric string or number is used to create
a new file, i.e. a change iin number signals end of a file.
Lines with anything else are to be ignored.

For this reason, I need to check the first column for one of the following:
1. A string of numerals, f.ex. "014"
2. A number, f.ex. 610
3. #N/A
4. Blank
5. Anything else

So, I need a safe way to tell if I have either 1 or 2, or one of the rest.

I have code that handles 1, 2 and 3 right now, but then I tried another
sample and found blanks instead of #N/A.

I am thinking now that I should first check for #N/A, then retrieve the
contents into a Variant variable and check for blank.
If it is neither, check variable if it is numeric.
If it is I have my numeric string, otherwise it is a 5.

Are there any possible surprises in this?

Any help would be appreciated.

Ragnar
 
If I understand correctly, the values shown in column A should be sorted out
as shown in column B.

A B
1 '014 Numeric String
2 610 Number
3 #N/A Other
4 Other
5 ljk Other

I believe that this formula will do it:
=IF(ISNUMBER(A1), "Number",
IF(OR(ISERROR(VALUE(A1)),ISBLANK(A1)),"Other","Numeric String"))

HTH.

-Dave Smith
 
Dave Smith said:
I believe that this formula will do it:
=IF(ISNUMBER(A1), "Number",
IF(OR(ISERROR(VALUE(A1)),ISBLANK(A1)),"Other","Numeric String"))
Click to expand...
....

Works, but could be done more compactly as

=IF(ISNUMBER(A1),"Number",IF(ISNUMBER(-A1),"Numeric String","Other"))
 
Harlan Grove said:
=IF(ISNUMBER(A1),"Number",IF(ISNUMBER(-A1),"Numeric String","Other"))
Click to expand...

Didn't think through blank cells. Make that

=IF(ISNUMBER(A1),"Number",IF(ISNUMBER(-(""&A1)),"Numeric String","Other"))
 
Thanks Dave and Harlan,

To start with, I guess I didn't make it clear exactly what I am trying to
do, and why I posted in the first place.

I am writing a VB6 application to do the work and in order to know where to
break up the original spreadsheet
into groups of rows to copy and thatn paste into a new spreadsheet file, I
use code to detect what is in each cell
of the first column.

The source worksheets have no formulas at all. I don't know how they are
generated, but I think spreadsheets
are used simply as a convenient way to display tabular data.

At first it looked very easy, just retrieve the content and compare it
against string values.
The first surprise came when I found that when I retrieved the content of a
cell that showed
#N/A I did not get "#N/A" but "Error 2042". I found that I should use the
WorkSheetFunction.IsNA
to detect #N/A and that works well.

I had also assumed that a blank cell would contain Null, so I checked it
with the VBA IsNull
function, which did not work. I am now using

Dim objSheet As Excel.Worksheet
Dim nRowIndex As Long
Dim vRegNum as Variant

If (objSheet.Cells(nRowIndex, 1) = Empty) Then

However, there seems to be more than one type of blank cell, since what
works fine in one
sheet does not in another, even though looking at the sheets the cells look
blank in both cases.

Once I have checked for #N/A or blank then I retrieve the contents into a
Variant

vRegNum = objSheet.Cells(nRowIndex, 1)

Just for good measure I check if vRegNum is null, and if not I convert it to
a string
Then I check if the string represents a number using IsNum.
At that point I have a region number to compare with the one from the
previous row.
If it is the same, I go to the next row, otherwise I know it is the end of a
region and
I copy and paste.

If I had found #N/A or blank, that signals the end of the sheet, move to
next sheet.

I have looked through the Excel help and I am aware that there is a lot of
possibilities
for what I can get when I retrieve the contens of cells so I just wanted to
know if there
are any other surprises in store.

As for your suggestion
=IF(ISNUMBER(A1),"Number",IF(ISNUMBER(-(""&A1)),"Numeric String","Other"))
Click to expand...

How would I use this formula?

Ragnar
 
Back
Top