I have created a function (NoT a sub routine) in Excel
that requires the cells in the range to contain only
numeric data. Therefore, if there are any cells that
contain letters, or even if a cell looks blank but
contains a space, the function does not work. Is there a
way that I can get the function to cells containing
anything non-numeric in the given range BEFORE it begins
its other calculations?

Eskima

Are blanks OK, but nonnumerics not? You can loop through the cells and
check for nonnumerics and return an error if it finds any. Here's and
example

Public Function AllNums(Rng As Range) As Double

Dim cell As Range

For Each cell In Rng.Cells
If Not IsNumeric(cell.Value) Then
AllNums = CVErr(2051)
Exit Function
End If
Next cell

AllNums = Application.Sum(Rng)

End Function

Blank cells will be treated as numeric because their values will evaluate to
zero.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Eskima" wrote in message
...
I have created a function (NoT a sub routine) in Excel
that requires the cells in the range to contain only
numeric data. Therefore, if there are any cells that
contain letters, or even if a cell looks blank but
contains a space, the function does not work. Is there a
way that I can get the function to cells containing
anything non-numeric in the given range BEFORE it begins
its other calculations?

My data sets are too large to do empty the cells manually,
so I've tried making some changes to the function you set
up, but it doesn't seem to do the clearing. You'll see
what I mean. Any more ideas?

Public Function AllNums2(Rng As Range) As Double

Dim cell As Range

For Each cell In Rng
If IsNumeric(cell.Value) = False Then
cell.ClearContents
End If
Next cell

AllNums2 = Application.Sum(Rng)

End Function


-----Original Message-----
Eskima

Are blanks OK, but nonnumerics not? You can loop through
the cells and
check for nonnumerics and return an error if it finds
any. Here's and
example

Public Function AllNums(Rng As Range) As Double

Dim cell As Range

For Each cell In Rng.Cells
If Not IsNumeric(cell.Value) Then
AllNums = CVErr(2051)
Exit Function
End If
Next cell

AllNums = Application.Sum(Rng)

End Function

Blank cells will be treated as numeric because their
values will evaluate to
zero.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.


"Eskima" wrote in message
...
I have created a function (NoT a sub routine) in Excel
that requires the cells in the range to contain only
numeric data. Therefore, if there are any cells that
contain letters, or even if a cell looks blank but
contains a space, the function does not work. Is there a
way that I can get the function to cells containing
anything non-numeric in the given range BEFORE it begins
its other calculations?


.

Eskima,

This clears everything except numeric entries if placed in a standard module
Range("A1:A8").SpecialCells(xlCellTypeConstants, 22).ClearContents
Range("A1:A8").SpecialCells(xlCellTypeFormulas, 23).ClearContents

this selects numeric entries if placed in a standard module
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Now maybe you can use this selection in your function.

steve

"Eskima" wrote in message
...
I have created a function (NoT a sub routine) in Excel
that requires the cells in the range to contain only
numeric data. Therefore, if there are any cells that
contain letters, or even if a cell looks blank but
contains a space, the function does not work. Is there a
way that I can get the function to cells containing
anything non-numeric in the given range BEFORE it begins
its other calculations?