Concatenate string in VBA not working with R1C1 reference

[RockandGrohl]

Hi all,

Think it's too early for me and I'm missing a trick.

I want to simply take a booking reference string and concatenate it with ' and ',

So S123456 becomes 'S123456',


Can anyone tell me why the below just results in

'',

Thanks.

Range(Cells(2, Lastcol + 1), Cells(Lastrow, Lastcol + 1)).FormulaR1C1 = "''" & RC15 & "',"

 

[RoryA]

Because you haven't built a real formula string, you've used RC15 as a VBA variable (if you had Option Explicit set, you'd have got an error). You'd need:

Range(Cells(2, Lastcol + 1), Cells(Lastrow, Lastcol + 1)).FormulaR1C1 = "=""'""&RC15&""',"""

 

[RockandGrohl]

Ah I see, yes that works. I do have to add a double apostrophe to get it to write the value down as 'S123456', as opposed to S123456', - it's for users to copy and slam into SQL.

Thanks for the help once more. I see so I used Formular1c1 but it wasn't really a formula, it was built the same way you'd compile a string like cells(1,6).value = ""''" & cells(activecell.row, "Z").value & ""',""