clearing non-numeric data
 

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?

clearing non-numeric data
 

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?

clearing non-numeric data
 

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?


.

clearing non-numeric data
 

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?


.

clearing non-numeric data
 

Ok, then how do I write a subroutine that will empty those
non-numeric cells, and then can call that subroutine from
my funtion? By the way, this was just a sample function
that I created, and my real function is for doing a custom
method of percentiles. If it runs into any cells that
contain nonnumeric data (blanks are ok) then I get a
#value! error. Your insight was awesome - a simple answer
I've been waiting for for a while. Any further help?

-----Original 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?

clearing non-numeric data
 

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?


.





.

clearing non-numeric data
 

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?

clearing non-numeric data
 

Eskima

You can't. The only a function can do is return a value. It can't even
call a sub that changes anything about the Excel environment. I'm sure it
was set up this way because if you could change other cells, it would really
screw up the calculation algorithm, e.g. change a cell that's already been
marked as calculated.

I see three options for you:
1. Write the function to treat non-numerics as blank. If you'd like to
post your function, I can help you modify it to do that. The problem here
is that you may get what seems like a good return value, but it's not
because there are non-numerics that are being ignored.

2. Educate the users that when they get a #VALUE error, that they need to
run a clean up sub with code similar to what Steve posted.

3. Tell the users they need to run that clean up sub. One of the few
things that a function can do is show a message box, so you could have code
that checks for the error, and if found, shows a message that the data is
not right and the sub needs to be run.

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

"Eskima" wrote in message
...
Ok, then how do I write a subroutine that will empty those
non-numeric cells, and then can call that subroutine from
my funtion? By the way, this was just a sample function
that I created, and my real function is for doing a custom
method of percentiles. If it runs into any cells that
contain nonnumeric data (blanks are ok) then I get a
#value! error. Your insight was awesome - a simple answer
I've been waiting for for a while. Any further help?

-----Original 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?

clearing non-numeric data
 

Thanks Dick. I'll just have to run them seperately then. I
always wondered about calling Subs from within Functions,
so thanks for clearing that up for me.
Case closed for now. Thanks a million.

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

You can't. The only a function can do is return a
value. It can't even
call a sub that changes anything about the Excel
environment. I'm sure it
was set up this way because if you could change other
cells, it would really
screw up the calculation algorithm, e.g. change a cell
that's already been
marked as calculated.

I see three options for you:
1. Write the function to treat non-numerics as blank.
If you'd like to
post your function, I can help you modify it to do that.
The problem here
is that you may get what seems like a good return value,
but it's not
because there are non-numerics that are being ignored.

2. Educate the users that when they get a #VALUE error,
that they need to
run a clean up sub with code similar to what Steve posted.

3. Tell the users they need to run that clean up sub.
One of the few
things that a function can do is show a message box, so
you could have code
that checks for the error, and if found, shows a message
that the data is
not right and the sub needs to be run.

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

"Eskima" wrote in message
...
Ok, then how do I write a subroutine that will empty
those
non-numeric cells, and then can call that subroutine
from
my funtion? By the way, this was just a sample function
that I created, and my real function is for doing a
custom
method of percentiles. If it runs into any cells that
contain nonnumeric data (blanks are ok) then I get a
#value! error. Your insight was awesome - a simple
answer
I've been waiting for for a while. Any further help?

-----Original 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?



.

clearing non-numeric data
 

Eskima,

Thanks for the kind words. Learned from experience to get down to the
basics. And lots of help from the gurus in the group.

I did fail to mention that you can't do stuff to the spreadsheet with a
function. You need a standard module. But you can tie the 2 together.

Wasn't sure of what you really wanted so I just pulled up a new workbook and
started playing with it.

Glad you got "your day made"!!!

steve

"Eskima" wrote in message
...
Thanks Steve,
This seems to work fine.
I love it when I get clear clean simple answers. Your's
was a prime example. Thanks a million, you made my day.

-----Original Message-----
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?


.