Consulting

Results 1 to 10 of 10

Thread: #Name? error for TextJoin UDF

  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    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.

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,597
    Location
    Where is the TextJoin UDF? In which workbook, and which module? Also, did you enable macros while opening the workbook?
    Be as you wish to seem

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,577
    Sub TextJoin() <> Function TextJoin()

    UDFunction

  4. #4
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    Location
    UDF and Macro are in the same workbook. Note: I changed the code in the question. I pasted wrong code by mistake. Sorry about that.

  5. #5
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    Location
    Now I deleted the macro, and recreated it in VBA, It shows =TextJoin(...) when I try to type, but #Name? error is still there in all cells.
    I am open to alternative suggestions instead of =TextJoin () funcion here. What I try to do is to concatenate cells meeting a specific condition as mentioned above in the formulas.

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,597
    Location
    Did you enable macros and did you remove the sub if that had the same name?
    Be as you wish to seem

  7. #7
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    Location
    Macros are enabled, there are other macros on the worksheet and they are fine. I don't understand your second line much, it has a unique name too.

  8. #8
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    Location
    I wasted too much time on this really. Is there any alternative method to achieve what I want to do here? I'd be glad if you can share your opinions. thanks.

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,577
    Learning is never wasted time.

  10. #10
    VBAX Regular
    Joined
    Aug 2017
    Posts
    48
    Location
    thanks. that is very right. By the way, I created a new workbook and transferred the contents of the problematic workbook including vba codes. and now TextJoin is working too. but I still don't have a clue why such a problem occurs. thanks for interest and time.

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
  •