Check if a cell include number or not

G

guyav

Hi,
I need to run a loop in Excel on a range (i.e A1:A8) and check by
TYPE if the cell include text or number.
In other words, if all cells are include numbers then give "ok", els
give "error"
I know how to use the TYPE, but I don't know how to combine it with
the loop.
Any help will be appreciated.
Thanks,
Guy
 
K

kkknie

If you are looking for a formula to put in a cell rather than usin
code, try this:

=IF(COUNT(A1:A8)/COUNTA(A1:A8)=1,"ok","err")

It counts the numbers using COUNT, then counts all cells using COUNTA.
If all of the cells are numbers, dividing the two will give a 1.
Otherwise it won't and the IF will return err.
 
D

Don Guillett

Despite what your instructor might have said, this is the way I would do it.
Extra points for creativity.

Sub checknums()
For Each c In Range("a1:a8")
If IsNumeric(c) Then
MsgBox c.Address & " is a number"
Else
MsgBox c.Address & " is NOT a number"
End If
Next
End Sub
 
K

kkknie

Not sure how to accomplish that since:

=TYPE(A1:A8)

Returns an error (type 16)

and if it is entered as an array formula (ctrl-shft-enter) as

{=TYPE(A1:A8)}

it returns an array (type 64).

If you really need to use TYPE, but can use code, try this variant o
Don's code converted to a function:

Code
-------------------
Function checknums(xRange as Range)
Dim r as Range
Dim b as Boolean
b=True

For Each r In xRange
If Application.WorksheetFunction.Type(r.Value) <> 1Then
b=False
exit for
End If
Next
checknums="ok"
if b=False then checknums="err"

End Functio
 
Top