Why wont my function extend my length by one if the value is non numeric

Question

So I thought I was tasked with a pretty simple objective however, it turns out I'm an struggling with it.

I have a really simple piece of code I want to execute and the idea behind the is numeric function means it skips cells that are not numeric and only places numerical values into the calculation. GREAT. However, I wanted it to extend the Length Variable by 1 so that for every blank it would add an extra number to keep the true value of Length the same.

however I can't get this to work. MY isnumeric function seems to do nothing.

Can someone help?

    Function RSE(MyCells As Range, Length As Double)  
    Dim up_day, down_day, ups, downs
    Dim average_up, average_down
    Dim rs, cellcount, rangecount As Long
    Dim cll As Range
    ups = 0
    up_day = 0
    downs = 0
    down_day = 0
    cellcount = 0
    rangecount = 0

For Each cll In MyCells


    If IsNumeric(cll) Then

    cellcount = cellcount + 1
    If cellcount = Length Then Exit For
    If cll.Value >= cll.Offset(1, 0).Value Then
    downs = downs + cll - cll.Offset(1, 0).Value
    ElseIf cll.Value < cll.Offset(1, 0).Value Then
    ups = ups + cll.Offset(1, 0).Value - cll.Value
    End If

    Else:

    Length = Length + 1

    End If

    Next cll
    average_up = ups / Length
    average_down = downs / Length

    rs = average_up / average_down

    RSE = 100 - (100 / (1 + rs))



    End Function

Empty cells can be evaluated as numbers (zero); hence IsNumeric[some_blank_cell]) will evaluate to True. — Ron Rosenfeld, Apr 29 '14 at 11:17
Thanks for the answer, so is it better that i check to see if the cell is blank rather than if it contains a number. — ImNewToThis, Apr 29 '14 at 11:24
If you are looking for blanks, then, by all means, test for that. — Ron Rosenfeld, Apr 29 '14 at 11:37

z̫͋ · Accepted Answer · 2014-04-29T15:42:36.083

1

You should use

If IsNumeric(cll) And Not isEmpty(cll) Then

since an empty cell is considered to be a numeric value

Re-Edit

Here's how I would do it

Function RSE(MyCells As Range, Length As Double)
    Dim up_day, down_day, ups, downs
    Dim average_up, average_down
    Dim rs, cellcount, rangecount As Long
    Dim cll As Range
    Dim nbBlank As Integer
    ups = 0
    up_day = 0
    downs = 0
    down_day = 0
    cellcount = 0
    rangecount = 0

For Each cll In MyCells

    If cll.Address <> MyCells.Cells(1, 1).Address Then 'skip first address
        If IsNumeric(cll) And Not IsEmpty(cll) Then

        cellcount = cellcount + 1
        If cellcount = Length Then Exit For
        If cll.Offset(-1 - nbBlank, 0).Value >= cll.Value Then
        downs = downs - cll + cll.Offset(-1 - nbBlank, 0).Value
        ElseIf cll.Offset(-1 - nbBlank, 0).Value < cll.Value Then
        ups = ups - cll.Offset(-1 - nbBlank, 0).Value + cll.Value
        End If
        nbBlank = 0

        Else:

        nbBlank = nbBlank + 1

        End If
    End If
    Next cll
    average_up = ups / Length
    average_down = downs / Length

    rs = average_up / average_down

    RSE = 100 - (100 / (1 + rs))

    End Function

edited Apr 29 '14 at 15:42

answered Apr 29 '14 at 11:35

z̫͋

1,531
10
15

Hi, After fixing the above error i realised another mistake i'd made but again i don't know the best fix. As the string of If's i made was cumbersome and didn't really work. Basically every time i find a blank the statement above uses the blank cell as the starting point for cll thus the answer is really off. How would i go about making the value of cll equal to the value of the cell prior to the last blank, if the value for cll is blank. – ImNewToThis Apr 29 '14 at 13:47
Hi I really appreciate the help and i've implement what you suggested and i can see what you are coming from. However, my two answers still differ greatly. For example one date set looks like this 100, blank, 102, 103, 104,103 and returns 1.94 (wrong) and the other one which is the same without the blank returns 80 which is correct. I just can't work out why and i know its because the blank is messing the how it works. As the values of the downs differ by 100. – ImNewToThis Apr 29 '14 at 14:46
@user2842059 You were right the blanks were messing the calculation. I edited my answer, I hope I got it right this time – z̫͋ Apr 29 '14 at 15:45
Thanks that worked amazingly well and very simply. :) – ImNewToThis Apr 29 '14 at 16:46

Why wont my function extend my length by one if the value is non numeric

1 Answers1

Re-Edit