How can I figure out if a cell contains numerical data or a string (text)? I need to compare some cells, and need my subroutine to be intelligent enough not to try to compare a number to a bunch of text. Thanks.

Jim

Jim,

Sub TestMe()
MsgBox isnumeric(Range("B8"))
End Sub

Will return True if it's numeric and False if not

John

"Jim Hollis" wrote in message
...
How can I figure out if a cell contains numerical data or a string (text)?
I need to compare some cells, and need my subroutine to be intelligent
enough not to try to compare a number to a bunch of text. Thanks.

Jim

Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.

--
Regards,
Tom Ogilvy




John Wilson wrote in message
...
Jim,

Sub TestMe()
MsgBox isnumeric(Range("B8"))
End Sub

Will return True if it's numeric and False if not

John

"Jim Hollis" wrote in message
...
How can I figure out if a cell contains numerical data or a string
(text)?
I need to compare some cells, and need my subroutine to be intelligent
enough not to try to compare a number to a bunch of text. Thanks.

Jim

"Tom Ogilvy" wrote in message
...
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.

--
Regards,
Tom Ogilvy




John Wilson wrote in message
...
Jim,

Sub TestMe()
MsgBox isnumeric(Range("B8"))
End Sub

Will return True if it's numeric and False if not

John

"Jim Hollis" wrote in message
...
How can I figure out if a cell contains numerical data or a string
(text)?
I need to compare some cells, and need my subroutine to be intelligent
enough not to try to compare a number to a bunch of text. Thanks.

Jim

Tom,

Thanks for the clarification.

John

"Tom Ogilvy" wrote in message
...
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.

--
Regards,
Tom Ogilvy




John Wilson wrote in message
...
Jim,

Sub TestMe()
MsgBox isnumeric(Range("B8"))
End Sub

Will return True if it's numeric and False if not

John

"Jim Hollis" wrote in message
...
How can I figure out if a cell contains numerical data or a string
(text)?
I need to compare some cells, and need my subroutine to be intelligent
enough not to try to compare a number to a bunch of text. Thanks.

Jim

"Tom Ogilvy" wrote...
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.

If the OP is testing cell values, wouldn't

VarType(Rng.Value) = vbDouble

be more efficient?

What did your tests show?

--
Regards,
Tom Ogilvy

Harlan Grove wrote in message
...
"Tom Ogilvy" wrote...
Actually:
? isnumeric("88")
True
? application.IsNumber("88")
False

isnumeric tests if the value can be interpreted as a number. the
worksheetfunction, isnumber tests if it is being stored as a number.

If the OP is testing cell values, wouldn't

VarType(Rng.Value) = vbDouble

be more efficient?

"Tom Ogilvy" wrote...
What did your tests show?
....
Harlan Grove wrote in message
"Tom Ogilvy" wrote...
....
? application.IsNumber("88")
False
....
VarType(Rng.Value) = vbDouble

I didn't time them - yet.

Interesting looking deeper into this. When dealing with Range objects in
VBA, should one use the .Value or the .Value2 property? If you pass the
IsNumber method the .Value property of a cell containing a positive number
formatted as date/time, it'll return FALSE since the .Value would be passed
to VBA as a Date type. If you pass the IsNumber method the .Value2 property,
on the other hand, it'll return TRUE. More interestingly, the IsNumber
method when passed a range reference alone, so neither the .Value nor the
..Value2 property, it seems to use the .Value2 property. In other words, when
the active cell is initially formatted as General, then the formula =NOW()
is entered into it,

Debug.Print Application.IsNumber(ActiveCell) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell)

gives

True
True

Debug.Print Application.IsNumber(ActiveCell.Value) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell. Value)

gives

False
False

Debug.Print Application.IsNumber(ActiveCell.Value2) & Chr(13) & _
Application.WorksheetFunction.IsNumber(ActiveCell. Value2)

gives

True
True

Digression: does this mean .Value2 rather than .Value is the default
property? Or does it mean that the IsNumber method when passed a range
reference chooses to use the .Value2 property rather than the .Value
property?

Only the OP could say for sure, but I'd guess for this sort of thing, the
..Value2 property would be what's wanted. So on to profiling.


Given the profiling macro


Sub foo()
Const MAXITER As Long = 500000
Dim i As Long, s As Boolean, dt As Date, et As Date

s = True
dt = Now
For i = 1 To MAXITER
s = s And Application.IsNumber(ActiveCell.Value2)
Next i
et = Now
Debug.Print "IsNumber: " & Format(86640 * (et - dt), "0.00")
Debug.Print s

s = True
dt = Now
For i = 1 To MAXITER
s = s And (VarType(ActiveCell.Value2) = vbDouble)
Next i
et = Now
Debug.Print "VarType: " & Format(86640 * (et - dt), "0.00")
Debug.Print s
End Sub


the results are

IsNumber: 16.04
True
VarType: 4.01
True

on my machine. Looks like VarType plus a comparison operation is
significantly faster than the IsNumber method call. However, this leaves the
deeper question of whether dates/times should be considered numbers.

use the typeName() function in VBasic to test the type of
data in the cell, and then test for text etc before
conitune processing (see under help, reference -
functions)

It may help to assign the Cell contents to a memory
variable first.


-----Original Message-----
How can I figure out if a cell contains numerical data or
a string (text)? I need to compare some cells, and need my
subroutine to be intelligent enough not to try to compare
a number to a bunch of text. Thanks.

Jim
.