my join function doesn't work

**dschmitt** · 03-14-2010, 09:34 PM

I am trying to combine several author names semicolon delimited into one cell. The input are author names that are placed in individual cells. To illustrate the question I added an Excel sheet.

I have a large number of rows with varying numbers of cells containing authors that I all want to combine in this fashion.

I tried to solve the problem with the join function, but run into trouble. I can't resolve the error. It would be great if somebody could help me out.

Below is what I tried.

Range("F1") = Join("A1:C1", ";")

**mdbct** · 03-14-2010, 10:25 PM

JOIN and SPLIT are possible in VBA but are not standard functions.
You could use something like this:

=LEFT(A2 &";"&B2&";"&C2&";"&D2&";"&E2,LEN(A2 &";"&B2&";"&C2&";"&D2&";"&E2)-(5-COUNTA(A2:E2)))

Change the cells to suit your needs. And change the 5 in the 5-COUNTA to the number of cells in the row.

For the A, B, and c columns it would be:

=LEFT(A2 &";"&B2&";"&C2,LEN(A2 &";"&B2&";"&C2)-(3-COUNTA(A2:C2)))

**mdbct** · 03-14-2010, 10:27 PM

Oops just saw you want a VBA function?
Let me work on that.

What was the error you were receiving?

**dschmitt** · 03-14-2010, 10:29 PM

Type mismatch

**mdbct** · 03-14-2010, 10:45 PM

The Type mismatch is because you are trying to assign an array to a range.

Here is a UDF.

Function JoinCell(myRange As Range) As String
Dim rCell As Range
For Each rCell In myRange
    JoinCell = JoinCell & ";" & rCell
Next
    JoinCell = Mid(JoinCell, 2)
End Function

**dschmitt** · 03-14-2010, 10:51 PM

I somewhat but not completely understand your code.
I think if you would change your code to be applicable in the example Excel sheet that I attached then I will undertand.

In the example Excel sheet I tried to combine the array A1:C1 in cell F1.

**mdbct** · 03-14-2010, 11:01 PM

You can in use it in two ways:
1) in the sheet itself in F1 enter =joincell(A1:C1)
2) in VBA

Range("F1") = Joincell(range("A1:C1"))

**dschmitt** · 03-15-2010, 12:17 AM

I finally got it done. It took a while to sink in.

I am still a beginner in VBA. I took a couple TurboPascal classes 20 year ago. And this is the first time I defined a function.

Function JoinCell(myRange As Range) As String
Dim rCell As Range
For Each rCell In myRange
    JoinCell = JoinCell & "; " & rCell
Next
    JoinCell = Mid(JoinCell, 2)
End Function

Sub Macro3()
Range("F1") = JoinCell(Range("A1:C1"))
End Sub