clearing non-numeric data

[Eskima]

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?

 

[Dick Kusleika]

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.

 

[Eskima]

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

 

[Dick Kusleika]

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.

 

[Eskima]

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?

 

[Eskima]

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!

 

[steve]

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

 

[Dick Kusleika]

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.

 

[Eskima]

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.

 

[steve]

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