如果所选范围不是来自单个工作表，则Excel UDF给出值错误

5cg8jx4n 于 2023-03-04 发布在其他

关注(0)|答案(1)|浏览(110)

下面的代码是用分隔符连接单元格的。这里的引用可以是一个连续的范围，也可以是一组放在括号内的范围。唯一的限制是，如果一组单元格包含来自两个不同工作表的范围，UDF将给出值错误。

Function CONCATENATEMULTIPLE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
Dim plc2remove As Long
plc2remove = Len(Separator)

For Each Cell In Ref
If Not Cell.Value = "" Then
 Result = Result & Cell.Value & Separator
 End If
Next Cell
If Result = "" Then
CONCATENATEMULTIPLE = "NO DATA TO SHOW"
Else
CONCATENATEMULTIPLE = Left(Result, Len(Result) - plc2remove)
End If
End Function

我认为解决办法是在迭代每个单元格时标识它的.parent属性，但我无法使其工作。
Concatenation works when group of ranges are from single sheet like this
But doesn't work when range from another sheet is also included like this

excel

来源：https://stackoverflow.com/questions/75600435/excel-udf-giving-value-error-if-selected-range-is-not-from-a-single-sheet

1条答案

按热度按时间

fhity93d1#

你可以这样做：

Function myConcat(Sep As String, ParamArray Ref() As Variant) As String
    Dim s, c, m As String
    m = ""
    For Each s In Ref
        For Each c In s.Cells
            m = m & c.Value
            If Len(c.Value) > 0 Then m = m & Sep
        Next
    Next
    If Len(m) = 0 Then
        myConcat= "NO DATA TO SHOW"
    Else
        myConcat = Left(m, Len(m) - Len(Sep))
    End If
End Function

它可以被称为类似于：

=myConcat(", ",Sheet1!A1,Sheet2!A2,Sheet1!A3,Sheet2!A1,A1:A2)

注意-范围未集中在括号中。

赞(0）回复(0）举报 2023-03-04

我来回答

如果所选范围不是来自单个工作表，则Excel UDF给出值错误

1条答案

相关问题

热门标签

最新问答