IsNumeric with array or range
 

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

IsNumeric with array or range
 

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

Jerry M wrote:

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

--

Dave Peterson

IsNumeric with array or range
 

Perhaps try

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


"Jerry M" wrote:

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

IsNumeric with array or range
 

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.

"Dave Peterson" wrote:

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

Jerry M wrote:

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

--

Dave Peterson

IsNumeric with array or range
 

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.



Jerry M wrote:

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.

"Dave Peterson" wrote:

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

Jerry M wrote:

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

--

Dave Peterson


--

Dave Peterson