textjoin help

[r121a947]

I am trying to create a textjoin macro that will recombine sorted words from a range of columns. The textjoin statement works in a single cell, but when I try to put it in a macro, I get a "syntax error" statement.

The macro code is:

Public Sub rejoinJ()

Dim i As Long
 
Dim lRow As Long

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Dim curRow As Long

curRow = 1

For i = curRow To lRow


TEXTJOIN(" ", TRUE, "N" & curRow   & ":BA" & curRow)
' MsgBox curRow

curRow = curRow + 1
Next i

End Sub

Thanks. Any help is greatly appreciated.

 

[Keetoowah]

Hi r121a947,
try this code, assuming you want to write the merged text in column B

Public Sub rejoinJ()
    
    Dim i As Long
    Dim lRow As Long
    Dim JoinedText As String
    
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
        
        JoinedText = WorksheetFunction.TextJoin(" ", True, Range("N" & i & ":BA" & i))
        Range("B" & i).Value = JoinedText '====> adapt the destination colum as needed

    Next i
    
End Sub

 

[r121a947]

Works perfectly!

Thank you.

 

[Keetoowah]

Hi,
Glad to help & thanks for the feedback