Avoiding isNumeric, plus summing dynamic ranges...

[London_Calling]

This is really dreadful coding, I know. The original idea was to avoid IsNumeric as it unhelpfully doesn't exclude blanks but, actually, my Or/And solution, as well as being hopeless, doesn't do what it's supposed to either.

In addition, as you may see from the later WorksheetFunction, the second idea is to sum a range (assuming there are no cells containing "/" or are blank) - and this is doomed as well, I suspect. Overall, it's a complete dogs breakfast:

If (resultsArr2(i + 1, 66) <> "/" And resultsArr2(i + 1, 66) <> "") Or (resultsArr2(i + 1, 67) <> "/" And resultsArr2(i + 1, 67) <> "") _
                                    Or (resultsArr2(i + 1, 68) <> "/" And resultsArr2(i + 1, 68) <> "") Or (resultsArr2(i + 1, 69) <> "/" And resultsArr2(i + 1, 69) <> "") Then

Ave = Round(WorksheetFunction.Sum(resultsArr2(i + 1, 26), resultsArr2(i + 1, 29), resultsArr2(i + 1, 32), resultsArr2(i + 1, 35), resultsArr2(i + 1, 38), resultsArr2(i + 1, 41), resultsArr2(i + 1, 44), resultsArr2(i + 1, 47), resultsArr2(i + 1, 50), resultsArr2(i + 1, 53)), 2) / counter2

It's all just awful. I suppose I'm really just hoping for some guidence and/or tip on more 'elegant' solutions to the IsNumeric and WorksheetFunction issues.

I would very much appreciate any help at all. Thanks.

 

[RoryA]

Not really clear why you wouldn't use IsNumeric in conjunction with Len to check if there is anything in the cell. Also, SUM ignores text and blanks anyway, so not really sure what the point of the tests is anyway?

 

[London_Calling]

Nor do I - who is Len, apart from the judge on that dancing show*


*only kidding. Len is probably the answer I thought IsNumeric was. Thanks

 

[RoryA]

Len returns the length of a variable, so:

If Len(resultsArr2(i + 1, 66)) > 0 and IsNumeric(resultsArr2(i + 1, 66)) Then

for example.

 

[London_Calling]

That's great, thanks.

The reason for the test was to find the average of cells not containing blanks or /. I understand AVERAGE and SUM ignore blancks but not /. Excluding the / and finding an average has been the issue. But I guess I got lost somewhere....

 

[RoryA]

Sum ignores any text including "/" so I'm not sure what your issue is there.