IsNumeric with array or range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry M - 24 Apr 2007 01:24 GMT

I want to check a list of cells to be sure they're all numbers before
performing a computation on them. Is there a quick way (as opposed to
looping through all the cells) to check? I could set the cells up as either
an array or range but IsNumeric doesn't seem to work on either as simply
IsNumeric(MyRange). I also tried

For Each cell In MyRange
If IsNumeric(cell.Value) Then GoTo ContinueToNextCell
a = 4
Exit For
ContinueToNextCell:
Next cell

but this doesn't work because it passes logical as well as numeric values
(apparently IsNumeric treats logical values as numbers?).

Also, I can't get IsLogical or IsNumeric to work (shouldn't I be able to use
these with "xl" in front?)

thanks,
Jerry

Reply to this Message

Dave Peterson - 24 Apr 2007 02:29 GMT

Dim myRng As Range
Set myRng = Range("a1:a10")
With myRng
If Application.Count(.Cells) = .Cells.Count Then
MsgBox "all numbers"
Else
MsgBox "not all numbers"
End If
End With

> I want to check a list of cells to be sure they're all numbers before
> performing a computation on them. Is there a quick way (as opposed to
[quoted text clipped - 17 lines]
> thanks,
> Jerry

Signature

Dave Peterson

Reply to this Message

Jerry M - 24 Apr 2007 07:40 GMT

Thanks. Both these answers worked (i can see they're basically the same.)
But I don't understand what it is about the Count property that has to do
with numerics. I thought it just counted # of cells in the range. Couldn't
find any info on it in my books or in Help.

> Dim myRng As Range
> Set myRng = Range("a1:a10")
[quoted text clipped - 27 lines]
> > thanks,
> > Jerry

Reply to this Message

Dave Peterson - 24 Apr 2007 13:29 GMT

myrng.cells.count is the number of cells in myrng.

application.count is using Excel's =count() function. See excel's help (not
VBA's) for more information.

> Thanks. Both these answers worked (i can see they're basically the same.)
> But I don't understand what it is about the Count property that has to do
[quoted text clipped - 36 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

JMB - 24 Apr 2007 02:32 GMT

Perhaps try

Application.Count(MyRange)=MyRange.Cells.Count

> I want to check a list of cells to be sure they're all numbers before
> performing a computation on them. Is there a quick way (as opposed to
[quoted text clipped - 17 lines]
> thanks,
> Jerry

Reply to this Message