Need to modify TextJoin code to populate not just the top row cell, but all the way down

[CaptainKen]

I got TextJoin to work in a cell and within code as shown below. What I need to do is to go to the next step, which would be populate the rest of the rows in column C based on values in columns A and B. The number of rows is an unknown. I assume I'd need a Loop or something, but I'm not that knowledgeable.

Column A: Movie name (300 Collection)
Column B: Movie path (movie sets\300 Collection)
Column C: Results populated in this column

This is an example of the desired output:
xcopy "movie sets\300 Collection\*.jpg" "v:\300 Collection" /i

This works from within a cell:
=TEXTJOIN("",TRUE, "xcopy """, "m:\", B1, "\*.jpg"" ""v:\", A1, """ /i")

This works as VBA, although is inserts a single quote as the first character

Dim strResult As Sring
Dim Sep As String

'Sep represents one double quotation mark
Sep = """"
'MsgBox Sep & "TEXT HERE" & Sep & Range("B1").Value & Sep

strResult = WorksheetFunction.TextJoin("", True, "xcopy ", Sep, "m:\", Range("B1").Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A1").Value, Sep, "/i")
MsgBox strResult
Range("C1").Value = strResult

 

[Peter_SSs]

Every piece of code you've supplied works the same, which is perfect.

Not sure who you are referring to there but I only provided one piece of code.

BTW, a few comments about using TEXTJOIN for this task

• CONCAT is a simpler function than using TEXTJOIN with "" as the delimiter
• vba has its own version of CONCAT (Join function) so if you are going to cycle through the data a row at a time it makes more sense to use vba's native function rather than the worksheet functions (CONCAT or TEXTJOIN)
• In vba you can also just concatenate text with an ampersand

So here is your original TEXTJOIN code line and options using the point noted above, each a little shorter than the last

strResult = WorksheetFunction.TextJoin("", True, "xcopy ", Sep, "m:\", Range("B1").Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A1").Value, Sep, "/i")
strResult = WorksheetFunction.Concat("xcopy ", Sep, "m:\", Range("B1").Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A1").Value, Sep, "/i")
strResult = Join(Array("xcopy ", Sep, "m:\", Range("B1").Value, "\*.jpg", Sep, Space(1), Sep, "v:\", Range("A1").Value, Sep, "/i"), "")
strResult = "xcopy " & Sep & "m:\" & Range("B1").Value & "\*.jpg" & Sep & Space(1) & Sep & "v:\" & Range("A1").Value & Sep & "/i"

 

[CaptainKen]

Apologies Peter, I missed that it wasn't Robert responding to each post. Both yours and Roberts worked great.

Thanks Peter for providing the multiple examples of how to achieve the same results.

I started with CONCAT, but then learned somewhere that when mixing in text and delimiters that it most likely wouldn't work, so TextJoin. I tried finding the site I read it at again to show you, but no luck.

 

[Peter_SSs]

Cheers.