Consulting

Results 1 to 10 of 10

Thread: #Name? error for TextJoin UDF

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    57
    Location

    #Name? error for TextJoin UDF

    I have this TextJoin macro in excel 2007. I used this UDF =TextJoin(...) to join cells on a specific condition like this:

    =TEXTJOIN(",",TRUE,IF(A6:E6<=7,A6:E6,""))
    =Iferror(TEXTJOIN(",",TRUE,IF((A6:E6<=18)*(A6:E6>=12),A6:E6,"")),"")
    =TEXTJOIN(",",TRUE,IF((A6:E6>=10),A6:E6,""))

    It worked for me at the beginning, I got my results, then a little while later, all cells showed #Name? error.
    I didn't change the code, or data. and I can't figure out a way to solve the problem.
    Now when I type =Textjoin in a cell, it doesn't even appear there as a function.

    Here is the code I used:

    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

    Any suggestions to solve the problem are greatly welcomed. thanks.
    Last edited by Mati44; 08-22-2017 at 05:05 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •