PDA

View Full Version : [SOLVED] #Name? error for TextJoin UDF



Mati44
08-22-2017, 04:50 AM
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.

Aflatoon
08-22-2017, 04:58 AM
Where is the TextJoin UDF? In which workbook, and which module? Also, did you enable macros while opening the workbook?

snb
08-22-2017, 05:02 AM
Sub TextJoin() <> Function TextJoin()

UDFunction

Mati44
08-22-2017, 05:07 AM
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.

Mati44
08-22-2017, 05:14 AM
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.

Aflatoon
08-22-2017, 06:25 AM
Did you enable macros and did you remove the sub if that had the same name?

Mati44
08-22-2017, 07:34 AM
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.

Mati44
08-22-2017, 08:30 AM
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.

snb
08-22-2017, 10:34 AM
Learning is never wasted time.

Mati44
08-22-2017, 10:56 AM
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.