In regards to designing the function to completely
disregard thses cells until the function is finished
executing, how do I do this? Its obviously not a matter of
entering "cell.Ignore" ..... too bad. Help!

Eskima
P.S. You guys have been great so far!

-----Original Message-----
Tom

The Sum function in my UDF was just for an example, I
assume the actual
function is something that can't be done with worksheet
formulas.

Eskima

You need to design your function to handle cells that
don't contain the
correct data. In my example, if any cell doesn't contain
the correct type
of data, an error is returned. You could also design it
to ignore those
cells if that's what you want.

I didn't get from your original question that you wanted
the function to
clear cells, but if that is what you were asking, then
see Tom's response
i.e. you can't do it.

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

"Tom Ogilvy" wrote in message
...
If you are putting this function in a worksheet as in

=AllNums2(A1:Z200)

then it will not clear any cells - because a function
used in a worksheet
can not change anything in the worksheet - it can only
return a value to
the
cell in which it is located (just like built in
functions).

Unless you have error values in your range, Sum(rng)
should work without
doing any clearing - sum ignores text and non-numeric
values (but not
errors
such as #N/A).

If you have errors, it would be easier to adjust your
formulas to not
return
errors

change
=formula
to

=if(iserror(formula),"",formula)

I have never seen sum have problems with an empty
string or a string that
is
not empty.

--
Regards,
Tom Ogilvy

"Eskima" wrote in message
...
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?


.





.