Function SumIntervalCols sometimes returns #value

[David Evans]

Ninjas -

Why would this code work in a cell and in an adjacent cell, return #value?

Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double
Dim arr As Variant
Dim total As Double
Dim j As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
total = total + arr(1, j)
Next
SumIntervalCols = total
End Function

 

[Debraj]

Can you please test this one..

Function SumIntervalCols(WorkRng As Range, interval As Integer) As Double
Dim arr As Variant
Dim total As Double
Dim j As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
    If IsNumeric(arr(1, j)) Then total = total + arr(1, j)
Next
SumIntervalCols = total
End Function

 

[David Evans]

Of course I will - be right back with an answer ...

 

[David Evans]

Debraj - you are a certifiable Ninja! Many thanks - now your secret sauce was this line - If IsNumeric(arr(1, j)) Then total = total + arr(1, j)
Am I correct in assuming that it "ignores" anything that is non-numeric?

 

[Debraj]

YES.. (#Value) is basically for Non-Numeric data's

Thanks for the feedback..

 

[David Evans]

YES.. (#Value) is basically for Non-Numeric data's

Thanks for the feedback..

As usual with Excel's little failures it was pilot induced - I shot myself in the foot with a little IF that placed a "-" in a cell if it was empty - Poor Practice, I can hear, Mr Weir saying in my ear ....
Thanks again for your willingness to help. It is one of the really great things about this forum is that people are universally kind and nice - there seems to be no knowledgeable boors around here, which is a rarity for the Internet!