VBA - Textjoin for the whole column

[Shukis7]

Hi,

I'm trying to use the Textjoin function in VBA.

I know how to do it for the first row, but I'm having issues when I want it to apply for the whole column.

For example, I want it to start at AH2 and the formula on the cell should be =TEXTJOIN("-",TRUE,I2,AE2,AF2,AG2).
For AH3 the formula should be =TEXTJOIN("-",TRUE,I3,AE3,AF3,AG3) and the same for AH4...
I know it should be something for i =1 in the range ... but I can't figure it out. (for each cell in range)

Any help would be really appreciated.

Thanks!

 

[kevin9999]

There's a number of ways you could do this, here's just one:

Sub Shukis7()
    Dim lr As Long
    lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
    With Range("AH2:AH" & lr)
        .FormulaR1C1 = "=TEXTJOIN("" - "",TRUE,RC9,RC31,RC32,RC33)"
        .Value2 = .Value2
    End With
End Sub

 

[Shukis7]

Wow, thank you so much! I'm still new to this and trying to learn. I really appreciate your help.

 

[kevin9999]

Wow, thank you so much! I'm still new to this and trying to learn. I really appreciate your help.

You're welcome, and thanks for the feedback ?

 

[Shukis7]

I have a similar issue with my next task, but this time instead of textjoin the formula is: =IF(COUNTIF($AH$2:AH2,AH2)>1,"Duplicate","Original")
The concept is the same, but now I am using only the column AH (RC34). I've tried modifying the previous formula, but it doesn't seem to work. I can start a new thread if you prefer. Thanks again!

 

[kevin9999]

Best to start a new thread, though due to time zone differences, it'll be some time before I get to it. You'll probably get assistance from others on the forum though.

 

[Shukis7]

No problem, thanks again for your help!