IsNumeric with array or range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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.
 
Back
Top