Thread: Join does not work

I have the following function

Code:

Function ConCatStr(MyRng As Range, MyDelimiter As String) As String
Dim Ccell As Range
  For Each Ccell In MyRng
    ConCatStr = ConCatStr & MyDelimiter & CStr(Ccell.Value)
  Next
ConCatStr = Right(ConCatStr, Len(ConCatStr) - Len(MyDelimiter))
End Function

This works, but I was looking at function JOIN() and came up with

Code:

Function ConCatStr2(MyRng As Range, MyDelimiter As String) As String
Dim MyArray
  MyArray = MyRng.Value
  ConCatStr2 = Join(MyArray, MyDelimiter)
End Function

Obviously the second has a lot of disadvantages, but I just wanted it to work with a single dimensional range A1:A3. Why doesn't it?

You would need to transpose your range such that you end up with a single dimension array

Code:

MyArray = Application.Transpose(MyRng.Value)

(were MyRng a horizontal vector rather than vertical vector then you would Transpose twice to get single dimension array)

Because if you assign the value of a range in one go like that, you always get a 2D array.

Well,

That sums it up.

THX RS and DO.

And you can't use Join with a Variant.

Originally Posted by shg

And you can't use Join with a Variant.

Shg - don't think that's correct. In the Immediate window the following works without issue:

Code:

v = Array(1,2,3)

?Join(v)

Richard

Ya know, that had always puzzled me. I reckon I never got the syntax correct:

Code:

Function CatVec(r As Range, Optional sSep As String = " ") As String
    With WorksheetFunction
        If r.Rows.Count > 1 And r.Columns.Count > 1 Then
            CatVec = "Vectors only!"
        ElseIf r.Rows.Count > 1 Then
            CatVec = Join(.Transpose(r.Value), sSep)
        ElseIf r.Columns.Count > 1 Then
            CatVec = Join(.Transpose(.Transpose(r.Value)), sSep)
        Else
            CatVec = CStr(r.Value)
        End If
    End With
End Function

Thank you, Richard.