pre-function cell clearance

  • Thread starter Thread starter Annika
  • Start date Start date
A

Annika

Here is the first chunk of my function. If there are any
blank cells that have any non-numeric characters in them
(whether just a space or an entire paragraph)I get the
#VALUE! error. I want to empty the cells in the range that
contain non-numeric data. How can I do this? What about
spaces before and after numeric data? i.e. a cell
contains " 43 " instead of simply "43" - This seems
simple enough, but I keep running into problems. Help!

Anyway, the function: More comments below.
************************************************
Function EPAPercentile(DataRange As Range, Offset As
Integer) As Single

Dim Datum As Object 'The value in the
cell of range
Dim NumberValues As Long 'The number of
values in the range
Dim Values() As Single 'The array used for
sorting
Dim Scratch As Single 'A temporary
variable used in the sorting algorithm
Dim TargetSubscript As Integer 'The offset into
the array of the required value
Dim i, j As Integer 'Loop Counters

'Initialize variables (array initialized after ReDim
statement)



NumberValues = 0
Scratch = 0
TargetSubscript = 0
i = j = 0


'Count number of values in range

For Each Datum In DataRange
If IsEmpty(Datum.Value) = False Then
NumberValues = NumberValues + 1
End If
Next Datum

'Make an array to hold them. Remember that we are using 0
as the subscript base

ReDim Values(NumberValues - 1)

'Initialize the array

For i = 0 To (NumberValues - 1) Step 1
Values(i) = 0
Next i

'Fill the array

j = 0

For Each Datum In DataRange
If IsEmpty(Datum.Value) = False Then
Values(j) = Datum.Value
j = j + 1
End If
Next Datum
********************************************************

It goes on and on and on, but I won't bore you. I want the
cells to be cleared before anything else happens, so the
first chunk is what's important. Any help would be greatly
appreciated.
 
Back
Top