# Function SumIntervalCols sometimes returns #value

#### David Evans

##### Active Member
Ninjas -

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

Code:
``````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``````

Last edited by a moderator:

#### Debraj

##### Excel Ninja
Can you please test this one..

Code:
``````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

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

#### David Evans

##### Active Member
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

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

Thanks for the feedback..

#### David Evans

##### Active Member
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!