0

This Solution nearly solve my problem but one small problem remains.

Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "-"): Next
  MyConcat = Left(xx, Len(xx) - 1)
End Function

If all of selected cell values are empty then in excel sheet the cell output showing #value. I want to show a dash if all cell are empty. How can i solve this problem?

Community
  • 1
  • 1
karim_fci
  • 1,212
  • 2
  • 17
  • 36

1 Answers1

2

There are really two fixes you can apply in your case:

The first fix is to modify your UDF to detect the error condition before it returns, then just make sure your UDF returns the string "-":

Function MyConcat(ConcatArea As Range) As String
  For Each x In ConcatArea: xx = IIf(x = "", xx & "", xx & x & "-"): Next
  If Len(xx) = 0 Then
    MyConcat = "-"
  Else
    MyConcat = Left(xx, Len(xx) - 1)
  End If
End Function

The second fix works inside the worksheet cell by wrapping your call to the UDF within an error checking formula. Here's an example:

=IFERROR(myudf(xxxx), "-")

EDIT: added error checking to your OP code. I would also recommend breaking your single-line If statement into multiple lines. As a general practice, it makes your code far easier to debug. Stepping through your UDF showed me that the #Value error you received was because you were passing a Length parameter of -1 to the Left function.

PeterT
  • 8,232
  • 1
  • 17
  • 38