• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
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?
 

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!
 
Top