summing comma separated values in a range
here's a UDF, worked on your sample...a string.."16,2,10,1,3,0,4"
Option Explicit
Public Function ColumnsSum(Source As Range) As String
Dim Result As String
Dim aResult() As Variant
Dim arr() As Long
Dim index As Long
Dim rowindex As Long
Dim ThisRow As Range
Dim splitter As Variant
Dim maxdepth As Long
Dim maxwidth As Long
maxdepth = Source.Rows.Count
For Each ThisRow In Source.Rows
splitter = Split(ThisRow.Value, ",")
rowindex = rowindex + 1
maxwidth = UBound(splitter, 1)
ReDim Preserve arr(1 To maxdepth, 0 To maxwidth)
For index = 0 To maxwidth
arr(rowindex, index) = splitter(index)
Next
Next
ReDim aResult(0 To maxwidth)
For rowindex = 1 To maxdepth
For index = 0 To maxwidth
aResult(index) = aResult(index) + arr(rowindex, index)
Next
Next
Result = Join(aResult, ",")
ColumnsSum = Result
End Function
NOTE if you wnat the output as an array,
remove
Result = Join(aResult, ",")
change
ColumnsSum = Result
to ColumnsSum = aResult
then change the function return type to Variant
"bthoron" wrote in message
ups.com...
I have a range of cells filled with comma separated values e.g.
8,2,0,0,1,0,0
3,0,7,1,0,0,2
5,0,3,0,2,0,2
I'd like to be able to total the values:
16,2,10,1,3,0,4
Right now I use a function that adds the 1st argument of each
individual cell (I have to identify each one) and the then a second
function for the next argument and so on... (too much typing of ab123
etc. I'd really like to simplify the work and be able to have one
function work for any range of cells.
The reason for the comma separated values was an attempt to make the
data easier to read (fewer columns).
thanks
|