利用像TextJoin这样没有office 365的功能？

Question

我在A1:C1中有标题，在A2:C5中有值(X)。范围可能不同，但我希望D列在发现范围内的/时，显示出由X分隔的标头值。

插入此vba函数是因为我有一个较旧版本的excel，而没有textjoin集成：

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

这个公式由Scott给出，以便将头放入数组中：

压下=TEXTJOIN("/",TRUE,IF(A2:C2="X",$A$1:$C$1,""))后ctrl+shift+enter

如何省略#value!错误？我试图包装一个iferror语句，但这只是在单元格中填充任何内容。

​

​

Answer 1

Function TextJoin(delimiter As String, ignore_empty As Boolean, ParamArray PAitems() As Variant) As String
 'like TextJoin in office 365
 If ignore_empty Then
  TextJoin = JoinIE(delimiter, PAitems)
 Else
  TextJoin = JoinKE(delimiter, PAitems)
 End If
End Function

Function JoinIE(delimiter As String, ParamArray PAitems() As Variant) As String
 'join ignore empty
 Dim v, w
 Dim s As String
 Dim j As String
 For Each v In PAitems
  If IsArray(v) Then
   For Each w In v
    j = JoinIE(delimiter, w)
    If Len(j) Then s = s & j & delimiter
   Next
   If Len(s) >= Len(delimiter) Then s = Left(s, Len(s) - Len(delimiter))
   v = s
   s = vbNullString
  End If
  If Not IsMissing(v) And Not IsError(v) Then
   If Len(v) Then JoinIE = JoinIE & v & delimiter
  End If
 Next
 If Len(JoinIE) <= Len(delimiter) Then Exit Function
 JoinIE = Left(JoinIE, Len(JoinIE) - Len(delimiter))
End Function

Function JoinKE(delimiter As String, ParamArray PAitems() As Variant) As String
 'join keep empty
 Dim v, w
 Dim s As String
 For Each v In PAitems
  If IsArray(v) Then
   For Each w In v
    s = s & JoinKE(delimiter, w) & delimiter
   Next
   If Len(s) >= Len(delimiter) Then s = Left(s, Len(s) - Len(delimiter))
   v = s
   s = vbNullString
  End If
  If IsMissing(v) Or IsError(v) Then v = Empty
  JoinKE = JoinKE & v & delimiter
 Next
 If Len(JoinKE) <= Len(delimiter) Then Exit Function
 JoinKE = Left(JoinKE, Len(JoinKE) - Len(delimiter))
End Function