Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values

Question

I'm using the textJoin UDF listed below to combine values from various rows into a single cell; it displays each value. However, I want to know if I can manipulate the output of this UDF so that instead of simply displaying the values I can add the values and get the sum of the values. Or ideally, if I can just modify variables to instruct it to ADD the values. Does anyone know if this UDF (which I didn't create) can be instructed to output the Cell names (A2, B2, C2), and if so can I put that output within the Sum() function so that it will add A2+B2+C2?

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
        TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
    End Function

It seems like there should be a way to convert the output from the value of the cell to the cell name (i.e. C2, C3, C4, etc), then put the whole thing within the Sum() function so that it simply adds the cells together. Alternatively, is there a function that ads the values placed within the function instead of using cell names?

I want to add the highlighted cell (G2). I have the value of 10 and 20. It seems like I should be able to use =Sum(textJoin(...)) if I can get textJoin to output the cell names (i.e. C2, C3).

score 1 · Accepted Answer · answered Jul 06 '17 at 20:20

1

if you want it to just total the values then you can add something like this to the end of the UDF

    Dim total As Long
    Dim txtPart
    For Each txtPart In Split(TEXTJOIN, delim)
        total = total + CLng(txtPart)       
    Next txtPart
    TEXTJOIN = total

example

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
    arr2 = arr.Value
Else
    arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0

If t >= 0 And y >= 0 Then
    For c = LBound(arr2, 1) To UBound(arr2, 1)
        For d = LBound(arr2, 1) To UBound(arr2, 2)
            If arr2(c, d) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
            End If
        Next d
    Next c
Else
    For c = LBound(arr2) To UBound(arr2)
        If arr2(c) <> "" Or Not skipblank Then
            TEXTJOIN = TEXTJOIN & arr2(c) & delim
        End If
    Next c
End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
    'add the below loop to add each number together
    Dim total As Long
    Dim txtPart
    For Each txtPart In Split(TEXTJOIN, delim)
        total = total + CLng(txtPart)

    Next txtPart
    TEXTJOIN = total
End Function

answered Jul 06 '17 at 20:20

Sorceri

7,870
1
29
38

How do I edit the code so that output displays digits to the hundredths. For example, right now it is rounding to the nearest whole number such that 20.55 + 10.22 = 31.000. I want it to round to the nearest hundredths (i.e. 30.77). Maybe I should ask this as a question? – Devil07 Jul 07 '17 at 19:05
They are. I tried "number", "currency", and "accounting" and moved the decimals over. I think I have to adjust the code to out put it to the hundredths place. It has the info because its rounding it correctly. – Devil07 Jul 07 '17 at 19:24
I think it has something to do with this https://www.techonthenet.com/excel/formulas/clng.php. I'm trying to figure out how to have it output the decimals to the hundredths. – Devil07 Jul 07 '17 at 19:29
@Devil07 Replace CLng with CDbl to convert them to doubles but make note that if the value is 10.000 Excel will remove the decimal and Zeros if the Number Format of the cell is not set to show the trailing decimal places. – Sorceri Jul 07 '17 at 20:11
I tried CDbl and CCur and neither seems to change the output. I'll post as question so I can show screenshot. – Devil07 Jul 07 '17 at 20:38

Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values

1 Answers1

Linked